Forum Discussion
Mark Date Table after everything
Hello Everyone,
I openned an old projet that was build since 5 years ago. I saw that "dimCalendar" wasn't marked as "Date Table", so I marked date table as date and assure that date column is ok with every dates.
I corrected all sliceres and only a few measures used "sameperiodlastyear". Comparing the values of KPI's, everything is ok.
There are other things I have to pay atencion?
I also disabled the auto "date/time" option.
Could you please help?
Hi Jmrc16, I believe you covered everything however I have few check points.
- Verify all time intelligence functions specially SAMEPERIODLASTYEAR DAX
- Review model relationships (1:* with Date)
- Remove legacy auto date tables if you have
- Format & optimize calendar columns
- Add hierarchies & sort orders
Thanks,
If you found this solution helpful, please consider giving it a Likeđź‘Ť and marking it as Accepted Solutionâś”. This helps improve visibility for others who may be encountering/facing same questions/issues.Hi Jmrc16
Marking dimCalendar as a proper Date Table and disabling the auto date/time option that’s essential for a stable time intelligence setup.
To make sure everything runs smoothly going forward, here are a few key checks:
-
Ensure your date table has a complete, continuous range without gaps (no missing dates).
-
Check that all time intelligence measures like SAMEPERIODLASTYEAR are using this custom table it's more sensitive to gaps or incorrect setup than others.
-
Verify there's a proper 1-to-many relationship from the date table to all fact tables.
-
Remove any auto-generated hierarchies still used in visuals and replace with your own.
-
Use Sort by Column (e.g., Month Name by Month Number) to keep visual order correct.
-
Format your calendar columns (like Month, Quarter) if needed for consistency.
-
9 Replies
Hi Jmrc16, I believe you covered everything however I have few check points.
- Verify all time intelligence functions specially SAMEPERIODLASTYEAR DAX
- Review model relationships (1:* with Date)
- Remove legacy auto date tables if you have
- Format & optimize calendar columns
- Add hierarchies & sort orders
Thanks,
If you found this solution helpful, please consider giving it a Likeđź‘Ť and marking it as Accepted Solutionâś”. This helps improve visibility for others who may be encountering/facing same questions/issues.Hi ajaybabuinturi
Why SAMEPERIODLASTYEAR is much special than others?Hi Jmrc16,
Unlike other DAX(DATEADD, TOTALMTD, etc) functions, SAMEPERIODLASTYEAR DAX strictly depends on the date column's integrity. Like NO Missing dates, proper one-to-many relationship from date to fact tables. If the range isn’t continuous or has missing entries, it might return blank or incorrect results for periods that don’t align cleanly year over year.Thanks,
If you found this solution helpful, please consider giving it a Likeđź‘Ť and marking it as Accepted Solutionâś”. This helps improve visibility for others who may be encountering/facing same questions/issues.
This is an excellent upgrade! I was looking at the dashboards i created a few years ago the other day and i wasn't able to leverage the date table fully back then.
For your question I would verify if there are continuous date range and make sure there is no gaps in the calendar and it includes all dates needed by fact tables.
Since you are switching to all Time Intelligence Measures, it's worth testing a few more to compare with manual calculations for critical periods.
hello Jmrc16
I'm happy to hear that you put the time and effort to improve your report .
check your visuals for the auto hierarchical date created, and replace it with your own hierarchy
other than that and base on what you have said, you are good to go.
note: don't forget to use the sort by column feature to sort the column month name by the month nb.
Hi Jmrc16
Marking dimCalendar as a proper Date Table and disabling the auto date/time option that’s essential for a stable time intelligence setup.
To make sure everything runs smoothly going forward, here are a few key checks:
-
Ensure your date table has a complete, continuous range without gaps (no missing dates).
-
Check that all time intelligence measures like SAMEPERIODLASTYEAR are using this custom table it's more sensitive to gaps or incorrect setup than others.
-
Verify there's a proper 1-to-many relationship from the date table to all fact tables.
-
Remove any auto-generated hierarchies still used in visuals and replace with your own.
-
Use Sort by Column (e.g., Month Name by Month Number) to keep visual order correct.
-
Format your calendar columns (like Month, Quarter) if needed for consistency.
-
Hi Jmrc16
Thank you for reaching out to the Microsoft Fabric Forum Community.
ajaybabuinturi rohit1991 Daniel29195 MasonMA Thanks for the inputs and suggestions. I hope the information provided by the users was helpful. If you still have questions, please feel free to reach out to the community.
Thanks.
Hi Jmrc16
I wanted to check if you had the opportunity to review the information provided by users. Please feel free to contact us if you have any further questions.
Hi Jmrc16
Hope everything’s going smoothly on your end. We haven’t heard back from you, so I wanted to check if the issue got sorted If yes, marking the relevant solution would be awesome for others who might run into the same thing.
