Forum Discussion

Yonah's avatar
Yonah
Icon for Helper II rankHelper II
1 year ago

DAX SUM Sameperiod multiple Years

Hi,
I try to find a Solotion for the foling Problem:
I want to calculate a SUM for the Sameperido for multipley Years basesd on the maximum date.
So for Exampel, if my max. Date is 06.11.2024, I want to calculate the SUM until 06.11.2019, 06.11.2020, 06.11.2021 and so on.

This is my Solotion so far:

 

 

 

VAR Datum =
    CALCULATE ( MAX ( '01_SWOT'[Belegdatum] ), ALL ( '01_SWOT' ) )
VAR Datum_Jahr =
    MAX ( '01_SWOT'[Belegdatum] )
VAR TagimJahr =
    DATEDIFF (
        DATE ( YEAR ( Datum_Jahr ), 1, 1 ),
        DATE ( YEAR ( Datum_Jahr ), MONTH ( Datum ), DAY ( Datum ) ),
        DAY
    ) + 1
VAR JAHR =
    YEAR ( MAX ( '01_SWOT'[Belegdatum] ) ) - 1
VAR Umsatz_Vorjahr =
    CALCULATE (
        SUM ( '01_SWOT'[Gesamtumsatz] ),
        '00_Date'[Year] = Jahr,
        '00_Date'[DayNoOfYear] <= TagimJahr
    )
RETURN
    Umsatz_Vorjahr

 

 

 

But I don't think that this is the best  Way to Calculate it. 
So I would appreciate any Tips and Hints, for a more elegent Soulotion. 

Need Help

2 Replies

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

    Yonah 

    Optimized DAX solution:

    VAR MaxDate =
    CALCULATE(MAX('01_SWOT'[Belegdatum]), ALL('01_SWOT'))

    VAR YearList = 
    GENERATE(
    DISTINCT(SELECTCOLUMNS(ALL('00_Date'), "Year", '00_Date'[Year])),
    VAR CurrentYear = [Year]
    RETURN 
    ADDCOLUMNS(
    CALCULATETABLE(
    SUMMARIZE('01_SWOT', '01_SWOT'[Belegdatum], '01_SWOT'[Gesamtumsatz]),
    '00_Date'[Date] <= MaxDate,
    YEAR('01_SWOT'[Belegdatum]) = CurrentYear
    ),
    "Year", CurrentYear
    )
    )

    RETURN 
    SUMX(YearList, [Gesamtumsatz])

    💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
    Cheers,
    Kedar
    Connect on LinkedIn

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Yonah ,

     

    One thing I need to confirm: Do you want to calculate multiple outcomes?

    Here's the sample data:

    DateValue

    Tuesday, June 11, 2024 1
    Sunday, June 11, 2023 2
    Saturday, June 11, 2022 3
    Friday, June 11, 2021 4
    Thursday, June 11, 2020 5
    Tuesday, June 11, 2019 6

     

    The expected:

    DateValue

    Max Date Result
    Tuesday, June 11, 2024 21
    Sunday, June 11, 2023 20
    Saturday, June 11, 2022 18
    Friday, June 11, 2021 15
    Thursday, June 11, 2020 11
    Tuesday, June 11, 2019 6

     

    Is this the result? If not, please provide more detailed details.

     

     

    Best Regards,

    Stephen Tao

     

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