Skip to main content
cancel
Showing results for 
Search instead 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

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

View solution in original post

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

this worked perfectly - thank you!!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors