cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## DAX to calculate sum for same period for defined year

Does anyone know how to create a measure/variable that would calculate the sum for a defined year, while still keeping the month filters applied?

Phrased another way, I want to do "year-over-year" comparisons for each month in 2020 and 2021, but I want to compare them all to the same month in 2019 (i.e., pre-covid) rather than the previous year. So it would calculate the growth between, e.g., July 2020 vs July 2019, and between June 2021 vs. June 2019, etc.

1 ACCEPTED SOLUTION
Anonymous
Not applicable
``````[Comparison to Base Year] =
// The Year column in your calendar
// must be an integer for this to
// work.
var CurrentYear = SELECTEDVALUE( YourCalendar[Year] )
// Change this setting if you want to make
// any other year the base one. You can even
// have a slicer with years (disconnected table)
// that will tell this measure which year to
// use as the base. You would harvest the value
// of the Base Year from the slicer and use here.
// For the time being it's hard-coded, of course.
var BaseYear = 2019
var Result =
if( NOT ISBLANK( CurrentYear ),
var YearsToGoBack = BaseYear - CurrentYear
var CurrentValue = [YourMeasure]
var BaseYearValue =
CALCULATE(
[YourMeasure],
'YourCalendar'[Date],
YearsToGoBack,
YEAR
)
)
var Growth_ =
DIVIDE(
CurrentValue - BaseYearValue,
BaseYearValue
)
return
Growth_
)
return
Result``````
2 REPLIES 2
Anonymous
Not applicable
``````[Comparison to Base Year] =
// The Year column in your calendar
// must be an integer for this to
// work.
var CurrentYear = SELECTEDVALUE( YourCalendar[Year] )
// Change this setting if you want to make
// any other year the base one. You can even
// have a slicer with years (disconnected table)
// that will tell this measure which year to
// use as the base. You would harvest the value
// of the Base Year from the slicer and use here.
// For the time being it's hard-coded, of course.
var BaseYear = 2019
var Result =
if( NOT ISBLANK( CurrentYear ),
var YearsToGoBack = BaseYear - CurrentYear
var CurrentValue = [YourMeasure]
var BaseYearValue =
CALCULATE(
[YourMeasure],
'YourCalendar'[Date],
YearsToGoBack,
YEAR
)
)
var Growth_ =
DIVIDE(
CurrentValue - BaseYearValue,
BaseYearValue
)
return
Growth_
)
return
Result``````
Frequent Visitor

this worked perfectly - thank you!!