Last Year vs. YTD on a daily basis

[tlehner926]

Hi everyone,

Think I screwed something within my measures and I'm not able to fix it. I follow tutorials like https://community.powerbi.com/t5/Community-Blog/Dynamically-comparing-current-totals-to-last-year-to... to compare YTD vs LY and I'm nearly there but on the last meters I struggle.

Using my measures I get the following visual and please see within the graphics my issue. My year to date measure works flawless, my "last year" measure is showing me the cummulated data till end of the current month from last year.

I've created as recommended a dedicated calendar table using this expression and all my tables are in relationship to this calender:

Kalender =
GENERATE (
CALENDAR( DATE( 2007;1;1 ); NOW());
VAR currentDay = [Date]
VAR day = DAY( currentDay )
VAR month = MONTH ( currentDay )
VAR year = YEAR ( currentDay )
RETURN ROW (
"day"; day;
"month"; month;
"year"; year )
)

Here are my measures

Total Units LY = CALCULATE([Quantity Units]; SAMEPERIODLASTYEAR(Kalender[Date]))

Quantity Surgical Guides YTD =
IF(
ISFILTERED('Kalender'[Date]);
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column.");
TOTALYTD([Quantity Units]; 'Kalender'[Date].[Date])
)

Can anyone please point me into the right direction? Any help is greatly appreciated,

Tobias

[Ashish_Mathur · Accepted solution]

Hi,

Your measures should be:

Total units YTD = CALCULATE(SUM(Data[Units sold]),DATESYTD(Calendar[Date],"31/12"))

Total units YTD (Last year) = CALCULATE([Total unit YTD],SAMEPERIODLASTYEAR(Calendar[Date]))

Hope this helps.

[v-shex-msft]

Hi @tlehner926.

According to your description, I found you are try to use time intelligence function to calculate with not existed date in calendar. I think it may caused the issue.

Can you please provide some sample data/pbix file for testing? It will be help for clarify your issue and testing.

BTW, you can also take a look at following links about time intelligence functions:

Time Intelligence in Power BI Desktop

Secret of Time Intelligence Functions in Power BI

Regards,

Xiaoxin Sheng

[tlehner926]

Thanks for this input - good insights especially on mark as date.

THanks