Forum Discussion

TotalAnonymous's avatar
TotalAnonymous
Icon for Helper III rankHelper III
1 year ago
Solved

Previous Year measure - Function Dateadd expects a Contiguous Selection

Dear Community, I hope you're doing well. I faced an issue regarding DAX calculation. Along with this post, I've attached the PBIX through this link. The data in here is dummy but the struct...
Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
1 year ago

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

 

 

  • TotalAnonymous's avatar
    TotalAnonymous
    Icon for Helper III rankHelper III
    1 year ago

    Hi Jihwan, thanks for sharing. Sorry just replied your message. Please let me try your solution and come back to you ASAP. Thank you

    • TotalAnonymous's avatar
      TotalAnonymous
      Icon for Helper III rankHelper III
      1 year ago

      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 you

      • v-ssriganesh's avatar
        v-ssriganesh
        Icon for Community Support rankCommunity Support
        1 year ago

        Hi 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.