Forum Discussion

Janx's avatar
Janx
Regular Visitor
10 years ago
Solved

Contiguous date selection error - YTD LY

Hello all,   I have a problem with the YTD function. In a nutshell I have a table with Brand, Category, Franchise, Value, KPI etc.   I have created a YTD function YTD = TOTALYTD(SUM('Per Day'[Va...
kcantor's avatar
kcantor
Community Champion
10 years ago

Janx

Most often, issues with time intelligence come from the lack of a date dimension table. A seperate date table with all dates in the range is requried for that to work. If you are pulling the date from the fact table, you may not have information for weekends, holidays, etc. Check this link for more information.

http://www.powerpivotpro.com/2011/11/the-ultimate-date-table/

From your calculation, it looks like you are pulling the date from your fact table. Add a date table and give it another go.

Janx's avatar
Janx
Regular Visitor
10 years ago

kcantor

Thanks a lot! Yes, that's correct. I'm pulling the date from my fact table. What I did now is to download this DateStream table and I've imported it to Power BI. Then I've linked the date column in my fact table with the date column in the date table (exact same format). Do I need to do something else? Because apparently, it's still not working.

  • kcantor's avatar
    kcantor
    Community Champion
    10 years ago

    Janx

    You will need to change your DAX to reflect the new relationship. You have:

    YTD LY = CALCULATE([YTD],SAMEPERIODLASTYEAR('Per Day'[MonthYear]))

    It should now be something like:

    YTD LY = CALCULATE([YTD],SAMEPERIODLASTYEAR('Date'[DateKey]))

    I am also a fan of:

    YTD LY = CALCULATE([YTD],DATEADD('Date'[DateKey], -1, year))

     

    One example only. You will need to change both reflecting table and column names. Be sure to use the Date Key for the relationship and the DAX.

    • Janx's avatar
      Janx
      Regular Visitor
      10 years ago

      kcantor

      That's what I did, but unfortunately it still says the data is not contiguous. YTD is working, but YTD LY is not.

      • v-qiuyu-msft's avatar
        v-qiuyu-msft
        Community Support
        10 years ago

        Hi Janx,

         

        Based on my test, if the fact table date column is linked to the calendar table date column and the date column within the calendar table is continued, both YTD and YTD LY expressions can work. Please check attached .PBIX file. Compare it with your report to find where is the issue.

         

        If you have any question, please feel free to ask.

         

        Best Regards,
        Qiuyun Yu