Forum Discussion

remu776's avatar
remu776
New Member
2 years ago
Solved

YTD Formula

Hi all,

 

I have a table with a couple fields. Customer Name, Sales, Month, and Year. These fields are all related, as in month and year creates the timeframe where the transaction occurred (2 and 2022) is February 2022. I am trying to calculate YTD sales for each customer based on the month/year selected by my slicer. Does anyone have any insight?

 

Thanks

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi remu776 ,

     

    I think you need to create an unrelated DimDate table to help calculation.

    Data model:

    Measure:

    YTD = 
VAR _YEARSTART = DATE(SELECTEDVALUE('Unrelated DimDate'[Year]),01,01)
VAR _YEAREND = MAX('Unrelated DimDate'[Date])
RETURN
CALCULATE(TOTALYTD(SUM('Table'[Value]),DimDate[Date]),FILTER(DimDate,DimDate[Date]>=_YEARSTART && DimDate[Date]<=_YEAREND))

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

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

     

6 Replies

  • _AAndrade's avatar
    _AAndrade
    Resident Rockstar
    2 years ago

    Hi,

    My advice is creating a Date Calendar and create a new column in your actual table to define the Date (day-month-year).
    After create one relation beteween the two tables and create a measure to YTD calculation.

    • remu776's avatar
      remu776
      New Member
      2 years ago

      Hi there,

       

      I created a new column in my dataset which contains a date using the excel formula date, and i have the following formula now:

      YTD USD = totalytd(sum('Table'[USD]),'Table'[New Date],'Table'[Customer])
       
      but, when applying the formula, I get the following error: the New Date is the new date I created in excel

       

       

       

      • remu776's avatar
        remu776
        New Member
        2 years ago

        Hi everyone, I have updated my formula to get it like this now

        YTD_Total = TOTALYTD(SUM('Table'[USD]), 'Table'[New Date])
         
        My New Date field is a date that I have changed to locale in my query in PowerBI. However, now it does not calculate properly. When there are values in previous months, some months after are blank which should not be the case for YTD values. I also would like to add a customer filter here
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi remu776 ,

     

    I think you need to create an unrelated DimDate table to help calculation.

    Data model:

    Measure:

    YTD = 
VAR _YEARSTART = DATE(SELECTEDVALUE('Unrelated DimDate'[Year]),01,01)
VAR _YEAREND = MAX('Unrelated DimDate'[Date])
RETURN
CALCULATE(TOTALYTD(SUM('Table'[Value]),DimDate[Date]),FILTER(DimDate,DimDate[Date]>=_YEARSTART && DimDate[Date]<=_YEAREND))

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

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