Measure calculating difference to previous period

[akowalski]

Hello,

I have a problem calculating a measure that calculates difference to previous period(year). Years are selected in a slicer (generally 2 but would be great if the code worked with more periods). This code works for 2 latest year, but is not working even with 2 following years in the middle. For years with a gap (2015 and 2017) chart also shows year 2016.

I cannot find a way to adjust the code to work with the previous period selected from a slicer, not hardcoded.

Thank you for your help!

Penetration % YoY% = 
	VAR __PREV_YEAR =
		CALCULATE(
			SUM('output_ALL_2018p03'[Penetration %]);
			DATEADD('output_ALL_2018p03'[Date].[Date]; -1; YEAR)
		)
	RETURN
IF(SUM('output_ALL_2018p03'[Penetration %]) - __PREV_YEAR = 
    SUM('output_ALL_2018p03'[Penetration %]);BLANK();SUM('output_ALL_2018p03'[Penetration %]) - __PREV_YEAR )

[v-yuta-msft]

Hi akowalski,

Here is a general solution based on your description for your reference.

To sum up values based on selected date in slicer, create a calculate column using DAX formula like this:

Date2Year =
YEAR ( 'output_ALL_2018p03'[Date] )

Then modify your first measure like below:

VAR __PREV_YEAR =
CALCULATE (
    SUM ( 'output_ALL_2018p03'[Penetration %] ),
    ALLSELECTED ( 'output_ALL_2018p03[Date2Year]' )
)

Regards,

Jimmy Tao

[akowalski]

I tried to solve the problem by adding a measure calculating difference between max and min year.

Diff = MIN(output_ALL_2018p03[Date2Year]) - MAX(output_ALL_2018p03[Date2Year])

It is calculaing properly, but when i i try to add this variable into DateAdd function or even Switch and compare it to numbers (-1,-2,-3) it is not working at all in Switch and in Dateadd it is showing line of zeros.

CALCULATE(SUM('output_ALL_2018p03'[Penetration %]);DATEADD('output_ALL_2018p03'[Date].[Date]; Diff; YEAR))

[v-yuta-msft · Accepted solution]

Hi akowalski ,

In your DAX formula, Min() and Max() are calculated in a same row context which have only one row every iteration, so the filter result is nothing. The aggregation function like Min(), Max() or Sum() should be calculated in every filter context.

Regards,

Jimmy Tao