Mark Date Table after everything

[Jmrc16]

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?
 

[ajaybabuinturi · Accepted solution]

Hi @Jmrc16, I believe you covered everything however I have few check points.

1. Verify all time intelligence functions specially SAMEPERIODLASTYEAR DAX
2. Review model relationships (1:* with Date)
3. Remove legacy auto date tables if you have
4. Format & optimize calendar columns
5. 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.

[v-priyankata]

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.

[v-priyankata]

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.

[v-priyankata]

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.

[rohit1991 · Accepted solution]

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.

[Daniel29195]

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.

[MasonMA]

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.

[ajaybabuinturi · Accepted solution]

Hi @Jmrc16, I believe you covered everything however I have few check points.

1. Verify all time intelligence functions specially SAMEPERIODLASTYEAR DAX
2. Review model relationships (1:* with Date)
3. Remove legacy auto date tables if you have
4. Format & optimize calendar columns
5. 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.

[Jmrc16]

Hi @ajaybabuinturi 

Why SAMEPERIODLASTYEAR is much special than others?

[ajaybabuinturi]

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.