Forum Discussion

eva_t1984's avatar
eva_t1984
Frequent Visitor
4 years ago
Solved

YTD Calculation - previous year/2 years ago

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's avatar
    v-chenwuz-msft
    4 years ago

    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.

2 Replies

  • daXtreme's avatar
    daXtreme
    Icon for Solution Sage rankSolution Sage
    4 years ago

    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.

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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.