Forum Discussion
Previous Year measure - Function Dateadd expects a Contiguous Selection
Hi,
one of ways to achieve this is to have a proper calendar table, and this contains continuous date column.I tried to create one more calendar table in the sample pbix file, and please check the below picture and the attached pbix file.
The data model is not well optimized, but I tried not to remove anything from your model, and add one more calendar table.calendar_new = VAR _newdate = ADDCOLUMNS ( CALENDAR ( DATE ( 2021, 1, 1 ), DATE ( 2025, 12, 31 ) ), "@startofmonth", EOMONTH ( [Date], -1 ) + 1 ) VAR _generate = GENERATE ( _newdate, FILTER ( 'Sample Calendar', 'Sample Calendar'[Start of Month] = [@startofmonth] ) ) RETURN _generateHi TotalAnonymous,
Thank you for your thoughtful follow-up and I'm glad to hear the original solution worked for you.
Yes, it’s common to work with fact tables that aren’t at daily granularity. In such cases, here are some best practices:- Even if your fact has only Week Number or Start of Month, link it to a proper date table to enable time intelligence functions like DATEADD and TOTALYTD.
- Map fact data to the calendar using Start of Month, Start of Week, or a Year-Month key.
- Use inactive relationships and USERELATIONSHIP() when needed to keep your model clean.
The key is making sure your calendar table is continuous and your joins to it are clean and consistent.
If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.
Hi,
one of ways to achieve this is to have a proper calendar table, and this contains continuous date column.
I tried to create one more calendar table in the sample pbix file, and please check the below picture and the attached pbix file.
The data model is not well optimized, but I tried not to remove anything from your model, and add one more calendar table.
calendar_new =
VAR _newdate =
ADDCOLUMNS (
CALENDAR ( DATE ( 2021, 1, 1 ), DATE ( 2025, 12, 31 ) ),
"@startofmonth", EOMONTH ( [Date], -1 ) + 1
)
VAR _generate =
GENERATE (
_newdate,
FILTER (
'Sample Calendar',
'Sample Calendar'[Start of Month] = [@startofmonth]
)
)
RETURN
_generate
Hi Jihwan, thanks for sharing. Sorry just replied your message. Please let me try your solution and come back to you ASAP. Thank you
Hi Jihwan_Kim , I've checked the solution and it works! Thanks. I will mark it as a solution. But, I have one question. You said that the data model is not optimized. I think the data model is not optimized because the case where the first source is in Weekly granular and doesn't have date field and the second source is in monthly granular and contains only start of the month (in Date).
Have you ever faced the case when your fact data is not in Date granular? If so, how did you tackle it and optimized your data model?
Thank youHi TotalAnonymous,
Thank you for your thoughtful follow-up and I'm glad to hear the original solution worked for you.
Yes, it’s common to work with fact tables that aren’t at daily granularity. In such cases, here are some best practices:- Even if your fact has only Week Number or Start of Month, link it to a proper date table to enable time intelligence functions like DATEADD and TOTALYTD.
- Map fact data to the calendar using Start of Month, Start of Week, or a Year-Month key.
- Use inactive relationships and USERELATIONSHIP() when needed to keep your model clean.
The key is making sure your calendar table is continuous and your joins to it are clean and consistent.
If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.
