YTD Calculation - previous year/2 years ago

[eva_t1984]

Hallo, 

I have a question: I am trying to calculate Booked Orders YTD for 2022, 2021 and 2020.

My data is imported from SQL Server and I have all data up to 09/15/2022 in Power BI. 

This is my calculation for 2020: it returns the data for 2020 until 09/14/2020 and I wonder if anybody can tell me why? Is it because 2020 was a leap year? How would I have to change my formular to be able to see YTD up until 09/15/2020?

CALCULATE([Booked 2022 YTD], DATEADD(order_date[Date], -2, YEAR),order_date[Dayoftheyear]<=DATEDIFF(DATE(YEAR(TODAY()),01,01),TODAY(),DAY) )

This is my calculation for 2021: - shows data until 09/15/2022, just like wanted

CALCULATE([Booked 2022 YTD], DATEADD(order_date[Date], -1, YEAR),order_date[Dayoftheyear]<=DATEDIFF(DATE(YEAR(TODAY()),01,01),TODAY(),DAY) )

Thank you so much in advance for your help and feedback!

[v-chenwuz-msft · Accepted solution]

Hi @eva_t1984 ,

Can you try some code like this:

2020

CALCULATE([Booked 2022 YTD], filter( order_date, [Date] <= date( year(today())-2, month(today()), day(today()) &&

[Date] >= date( year(today())-2, 1, 1)) 

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[daXtreme]

@eva_t1984 

Sorry to say that but this is not how you calculate such things in PBI. Please try to get familiar with how PBI works and its philosophy because currently you're simply doing it wrong and if you carry on like this it'll bite you rather sooner than later and you'll have to do what I'm suggesting here anyway.

You can start with this: Power BI Beginner Tutorial (Part 1 of 3) - Bing video

Roughly speaking, in PBI you don't hard code years (or any other pieces of info, for that matter) into your measures. You should never call a measure [Booked 2020 YTD]; rather, you'd call it [Booked YTD] without any indication of the year since it has to work with ANY YEAR. You should write a measure that is general enough to work with ANY piece of information when you bring it into a visual from a dimension table.