Forum Discussion

cosmicyes's avatar
cosmicyes
Icon for Helper II rankHelper II
1 year ago
Solved

Rolling YTD for previous year

Dear Power BI friends,

 

I am trying to write a measure that gives me the YTD value of my revenue relative to the previous year. Today is November 20, 2024, and I have the revenue in the measure "Umsatz". What I want is the revenue for the period from January 1, 2023, to November 20, 2023.

The measure should roll dynamically, meaning it should respond to changes in the calendar slicers.

I have managed to get it working halfway, but this way, the measure does not respond to changes in the calendar:

 

 

 

Umsatz YTD Vorjahr # =
CALCULATE (
    CALCULATE (
        [Umsatz],
        DATESYTD ( Kalender[Datum] )
    ),
    Kalender[Datum]
        = (
            DATE ( YEAR ( TODAY () ) - 1, MONTH (
                TODAY ()
            ), DAY (
                TODAY ()
            ) )
        ) - 1
)

 

Thank you for your help.
 

  • saurabhtd's avatar
    saurabhtd
    1 year ago

    cosmicyes I think this will work 

     

    Umsatz YTD Vorjahr = 
CALCULATE(
    [Umsatz],
    DATESBETWEEN(
        Kalender[Datum],
        DATE(YEAR(MAX(Kalender[Datum])) - 1, 1, 1), -- Start of the previous year
        DATE(YEAR(MAX(Kalender[Datum])) - 1, MONTH(MAX(Kalender[Datum])), DAY(MAX(Kalender[Datum]))) -- End date in the prior year
    )
)

    But if above formula is not filtering with changes in calendar. 
    Try below code

    Umsatz YTD Vorjahr =
CALCULATE (
    [Umsatz],
    DATESYTD ( DATEADD ( Kalender[Datum], -1, YEAR ), "12/31" )
)

     

6 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    1 year ago

    cosmicyes , Try using dax


    Umsatz YTD Vorjahr =
    CALCULATE (
    [Umsatz],
    DATESYTD (
    SAMEPERIODLASTYEAR ( Kalender[Datum] ),
    "11/20"
    )
    )

    • cosmicyes's avatar
      cosmicyes
      Icon for Helper II rankHelper II
      1 year ago

      bhanu_gautam 

      thank you very much - but 11/20 ist not flexible and rolling.
      What I mean is: it should also work if I select June 2024 in calender and should show the result by June 23 2023 and so on...
      Is this possible?

  • saurabhtd's avatar
    saurabhtd
    Icon for Resolver II rankResolver II
    1 year ago

    cosmicyes This DAX will give you result

    Umsatz YTD Vorjahr = 
CALCULATE(
    [Umsatz],
    SAMEPERIODLASTYEAR(
        DATESYTD(Kalender[Datum])
    )
)
    • cosmicyes's avatar
      cosmicyes
      Icon for Helper II rankHelper II
      1 year ago

      Dear bhanu_gautam 
      this looks really good and I hoped it worked.
      But it seems the measure delivers the revenue for the *whole* year 2023.
      Am I missing something?

      • saurabhtd's avatar
        saurabhtd
        Icon for Resolver II rankResolver II
        1 year ago

        cosmicyes I think this will work 

         

        Umsatz YTD Vorjahr = 
CALCULATE(
    [Umsatz],
    DATESBETWEEN(
        Kalender[Datum],
        DATE(YEAR(MAX(Kalender[Datum])) - 1, 1, 1), -- Start of the previous year
        DATE(YEAR(MAX(Kalender[Datum])) - 1, MONTH(MAX(Kalender[Datum])), DAY(MAX(Kalender[Datum]))) -- End date in the prior year
    )
)

        But if above formula is not filtering with changes in calendar. 
        Try below code

        Umsatz YTD Vorjahr =
CALCULATE (
    [Umsatz],
    DATESYTD ( DATEADD ( Kalender[Datum], -1, YEAR ), "12/31" )
)