Previous Year measure - Function Dateadd expects a Contiguous Selection

[TotalAnonymous]

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 structure is similar with what I face.

In this case, I have a sample data that have no date field. The field that related with time is Week Number. Therefore, I created another calendar called "Year Month" as a shared dimension between "Sample Data" table with "Sample Data - 2" table.

Afterward, I created measure called "Offtake" sales. However, I'm facing struggle to create Previous Year YTD because both "Sample Data" and "Sample Data - 2" don't contain date field.

On top of that, I face this error.
 

Have you ever face this error? Thank you

https://drive.google.com/file/d/1m_bGwkZmnJARZhgdTjivY_duA7i-eTVs/view?usp=sharing

[Jihwan_Kim · Accepted solution]

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

[v-ssriganesh]

Hi @TotalAnonymous,

Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to @Jihwan_Kim for sharing valuable insights.

Could you please confirm if your query has been resolved by the provided solution? If so, please mark it as the solution. This will help other community members solve similar problems faster.

Thank you.

[Jihwan_Kim · Accepted solution]

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]

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]

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 · Accepted solution]

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.