Calendar Date Relationship

Resolved · High Priority · Version 365

Thu 21 Mar 2024, 10:06 Delegate Martin said...

Martin has attended:
Power BI Modelling, Visualisation and Publishing course

Calendar Date Relationship

Hi Jens / Team, hope you're well.

I am building a model using a dim Calendar as the base for two fact datasets.

For some reason the dates are not aligning. I have not previously had this issue in other models. For example, the date in calendar is matching against a different date in one of the datasets.

To add further confusion, the measures I have made appear to be using the correct (calendar) dates to show values from the datasets.

I have emailed some screenshots of the data model and issue.

Thanks for any help,

Martin

Thu 21 Mar 2024, 17:04 Trainer Jens said...

RE: Calendar Date Relationship

Hi Martin,

Thank you for the forum question.

My first thoughts were "This is strange". I have never seen that the dates do not align as I can see in the screenshot of your table. Then I looked at the screenshot of the relationships.

My only explanation is that you may have a problem with the formats of your dates in the PM Utilisation table. Please check if the date format is the same as in your date table.

If it is not the same change it.

It also explains why the DAX measures are correct. If the problem is formats.

If it is the same, I would create a new file and just connect to the the date table and the Utilisation table and create the same table again. If the dates now align you have a problem in the data model.

You have many to many relationship and a bidirectional relationship to the date table and based on my experience this can course issues.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Tue 26 Mar 2024, 15:52 Delegate Martin said...

RE: Calendar Date Relationship

Hi Jens, thanks for the quick response. It is reassuring to hear you are also not sure!

There were no issues with dates and formats that I could see. In the end I have rebuilt the model in a new file. I have also simplified the data model, using a bridge table to avoid many to many relationships. Touch wood, the issue has resolved so far.

Once I have tested it fully (I have other issues to resolve) I will let you know and mark the issue resolved.
Fingers crossed it was some sort of issue with the relationships which is not problem now.

Training courses

	98.7 % recommend us
	More live stats...

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

Power BI tip:

Master the Power BI Custom Visuals

Power BI offers a variety of custom visuals created by the community and Microsoft. Explore these visuals to enhance your reports and dashboards. Whether it's a custom map, a timeline, or a unique chart type, custom visuals can add significant value to your visualizations.

View all Power BI hints and tips