How to sum value for fixed years

[RexaZii93]

Hi

I have a data table which has multiple years in it (2019 to 2022).

I would like to have a measure that if I chose the slicer from Jan 2022 to May 2022 on my date filter it should sum the vlaues on 2019, 2020 and 2021 years each for only Jan to May on these years so I can compare it in a chart. It also should be fixed on these years no substracting them.

This's what I have but it shows blank data:

CALCULATE (

SUM ( 'AMS Flights Data'[Total Pax (Manifest)] ),

FILTER (

ALLSELECTED ( 'AMS Flights Data' ),

'AMS Flights Data'[Actual.1].[Year] = 2019

)

)

Please help

[v-chenwuz-msft · Accepted solution]

Hi @RexaZii93 ,

You need create an independent calendar table first which has no relationship with the fact table. The same as the following screenshot.

Then create a measure like this:

Measure =
CALCULATE (
    SUM ( 'Fact table'[Values] ),
    FILTER (
        VALUES ( 'CALENDAR'[Date] ),
        MONTH ( [Date] )
            IN VALUES ( 'New calendar'[Month] )
                && DAY ( [Date] ) IN VALUES ( 'New calendar'[Day] )
    )
)

Result:

Pbix in the end you can refer.

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.

[tamerj1]

@RexaZii93 

Please follow similar case in this post

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Rolling-Cumulative-Total-Over-last-3-Year-bas...

[RexaZii93]

Hello Tamer

The post only shows how to calculate the values in the past three years (-3) so if I want a certain year lets say 2019 I would have to change -3 to -4 every year.

what I would like is to calculate values on fixed year lets say 2019. Moreover, it only calculate the months I Pick on my callendar date, so if I chose Jan to July for 2022 it automatically do the same for 2019.

[tamerj1]

@RexaZii93 

Please provide sample file with dummy data to work with.