topic Re: DAX to calculate sum for same period for defined year in DAX Commands and Tips

DAX to calculate sum for same period for defined year

hp0655 — Fri, 16 Jul 2021 18:17:53 GMT

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.

Re: DAX to calculate sum for same period for defined year

Anonymous — Fri, 16 Jul 2021 20:48:06 GMT

[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], DATEADD( 'YourCalendar'[Date], YearsToGoBack, YEAR ) ) var Growth_ = DIVIDE( CurrentValue - BaseYearValue, BaseYearValue ) return Growth_ ) return Result

Re: DAX to calculate sum for same period for defined year

hp0655 — Thu, 22 Jul 2021 13:27:24 GMT

this worked perfectly - thank you!!