Different Values between Published Report and Desktop report for YTD

[Anonymous]

Dear Power BI Community,

I have created a dashboard that provides a view over this month's figures and YTD figures. In Desktop version all values add up as they should. After publishing the report, I have realised that the Month figures are correct but YTD to date not. Opened Desktop version again, checked and in there I still could see correct values.

After several things I tried, I still couldn't see correct values. I finally got to understand where is this issue coming from, however I am not sure how to fix it to have both version of the report (Desktop version & Published version) showing correct values.

So what I have got to understand is that, the third element of the YTD formula is causing this. To calculate YTD I have created the following metric: YTD_Actuals = TOTALYTD(sum('A-4(PL)'[Value_groups]),'A-4(PL)'[Date],"01/03/2022")

So it seems that the third element which is YearEndDate, in Desktop version it reads the data from 01 March 2022, while in Published version it reads the data as 03 March 2022.

The quick fix to this is to change the formula for YTD changing the format for YearEndDate, having MM/DD/YYYY. This however gives me incorect values in Desktop version and correct values in the Published version.

I have tried using instead of "01/03/2022", a measure that picks max date when FY=FY22, so basically a measure that returns 01/03/2022, and format(measure,"mm/dd/yy") However Power BI doesn't like using a measure, or a format(measure) as third element of this formula.

Can anyone please help with this? Just looking for the formula for YTD that would take into account that published version reads 01/03/2022 as MM/DD/YY while the Desktop version reads that date DD/MM/YYYY. I have tried changing the regional settings but that didn't do anything, maybe I am missing something?

Many many thanks, hope someone can help me with this

[Anonymous]

Thank you lots for your reply, It crossed my mind that maybe I should use a calendar table. However for the time being I just managed to fix the issue by using a filter in totalytd formula instead of the YearEndDate that is hardcoded. In the future I will listen to your advice and use a Calendar Table.

Many thanks again

[otravers · Accepted solution]

It looks like you're trying to do time intelligence against the date column of the A-4(PL) fact table, which is generally speaking a bad idea. You need to have a date dimension (aka calendar table) where you'll define the end of fiscal year which will let you use time intelligence functions without hardcoding a specific date in them.

You can use either Power Query or DAX to generate your date dimension, google with return a bunch of blog posts and videos.