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

      • Janx's avatar
        Janx
        Regular Visitor
        10 years ago

        v-qiuyu-msft

        Thanks a lot for your reply.

         

        I also got to the point that I don't get the error anymore, HOWEVER - and this is also the case in your data - the YTD LY formula is not working correctly. For example, in your data set your 2016 data goes through June 2016. However, your YTD LY formula gives you the Revenues for whole 2015 instead of January - June 2015 only, which is the general idea of YTD comparison.

         

        I have the very same problem in my data. I've uploaded my .PBIX file here. I've also included the calculation without using the date table ("YTD ERROR") for reference. As you see, the YTD and YTD LY does not lead to an error, however, it's not calculating last year's YTD correctly. I think there is some error in the date linking... Any ideas?

         

        Thanks!