Accumulated graphic with annual comparison

[Anonymous]

Hello, 

I created two measures for my accumulated graphic that today is showing 2022x2021, one measure is calculating the sum of 2022 and the other is calculating the sum of 2021.

I need to improve the measure to use the filter of the year to change the comparison for 2023x2022, but without losing the previous view. I want to use a measure that I can use only de filter of the year and get the comparison of the filtered year with the previous year.

Follow the example below:

Thanks in advance

[tamerj1]

Hi @Anonymous 
Please refr to attached sample file. I hope it will help you find the workable solution for your case.

Sales Amount = SUMX ( Sales, Sales[Quantity] * Sales[Net Price] )

Sales Amount RT = 
CALCULATE ( 
    [Sales Amount],
    'Date'[Date] <= MAX ( 'Date'[Date] )
)

Sales Amount RT Selected Year = 
VAR SelectedYear = MAX ( Years[Year] ) 
VAR StartDate =
    CALCULATE ( 
        MIN ( 'Date'[Date] ),
        'Date'[Year] = SelectedYear,
        ALL ( 'Date' )
    )
RETURN
    CALCULATE ( 
        [Sales Amount RT],
        'Date'[Year] = SelectedYear,
        Sales[Order Date] >= StartDate
    )

Sales Amount RT Previous Year = 
VAR SelectedYear = MAX ( Years[Year] ) 
VAR StartDate =
    CALCULATE ( 
        MIN ( 'Date'[Date] ),
        'Date'[Year] = SelectedYear - 1,
        ALL ( 'Date' )
    )
RETURN
    CALCULATE ( 
        [Sales Amount RT],
        'Date'[Year] = SelectedYear - 1,
        Sales[Order Date] >= StartDate
    )

[Anonymous]

Are you hardcoding the Year filter in the Measures?

If yes, then just remove the year filter and then you can use just 1 measure and then in the Visual, throw Year as a Legend.

[Anonymous]

Hello @Anonymous 

Thank you for your answer!

Your solution works in a good way, but I'd like to know if it's possible when I filter the year 2022 it shows the 2022 x 2021, and when I select the year 2023 it shows the comparison 2023x2022 in the graphic. Is that possible?

Thanks a lot

[Anonymous · Accepted solution]

For the current year

Accumulated Packs =

CALCULATE(SUM(FactTable[MeasureName]))

For the previous year:

Accumulated Packs Previous Year =

CALCULATE(SUM(FactTable[MeasureName]) , PREVIOUSYEAR('DimCalendar'[DateKey]))

Alternate

Accumulated Packs Previous Year =

CALCULATE(SUM(FactTable[MeasureName]) , SAMEPERIODLASTYEAR('DimCalendar'[DateKey]))

[Anonymous]

Sure, create a new measure referencing old measure with Sameperiodlastyear filter. Then select it in the Values.

please mark my answer as a solution if it worked.