topic Re: I need to show year over year values and they should be responsive to filters in DAX Commands and Tips

I need to show year over year values and they should be responsive to filters

jsgambel — Tue, 06 Dec 2022 22:03:10 GMT

Hi all, new to Power BI, so thank you for your help.

I need help tweaking my DAX. I have a spreadsheet for a data source. It has a list of clients, years, region, and a measure.

Sample:

Year1	Key	Type	Region	Count	DateYear
2010	20039423	Breakaway	EUROPE	1	01/01/2010
2010	20048145	Still	NORTH AMERICA	1	01/01/2010
2010	20069080	Still	EUROPE	1	01/01/2010
2010	20199484	Ambiant	EUROPE	1	01/01/2010
2010	20200079	Breakaway	EUROPE	1	01/01/2010
2010	20200128	Ambiant	ASIA	1	01/01/2010
2010	20200213	Still	EUROPE	1	01/01/2010
2010	20200417	Ambiant	ASIA	1	01/01/2010
2010	20200698	Glass	ASIA	1	01/01/2010
2010	20200700	Glass	AUSTRALIA	1	01/01/2010
2010	20200728	Breakaway	EUROPE	1	01/01/2010
2010	20201136	Breakaway	ASIA	1	01/01/2010

I want to show Year over Year values. I was able to do this for just the Year (though the Total for my column isn't right):

The column definition:

zPrevYearCount3 = VAR PriorYear = MAX('Sheet1'[Open Year]) -1 VAR Result = SUMX( FILTER( ALL(Sheet1), 'Sheet1'[Open Year] = PriorYear ), 'Sheet1'[Count] ) RETURN Result

But, when I add the Region in, the results aren't right:

This isn't actually the ideal way I want to present it. I want to put the years across the top and Region/other attributes down the side, but I can't even get this to work (I assume this is easier like this rather than my preferred way). Thanks.

Re: I need to show year over year values and they should be responsive to filters

Greg_Deckler — Tue, 06 Dec 2022 22:25:01 GMT

jsgambel Try:

zPrevYearCount3 = VAR PriorYear = MAX('Sheet1'[Open Year]) -1 VAR Result = SUMX( FILTER( ALLEXCEPT(Sheet1,[Region]), 'Sheet1'[Open Year] = PriorYear ), 'Sheet1'[Count] ) RETURN Result

Re: I need to show year over year values and they should be responsive to filters

v-zhangti — Wed, 07 Dec 2022 07:32:07 GMT

Hi, jsgambel

Column:

zPrevYearCount3 = VAR PriorYear = MAX ( 'Sheet1'[Open Year] ) - 1 VAR Result = SUMX ( FILTER ( ALL ( Sheet1 ), 'Sheet1'[Open Year] = PriorYear && [Region] = EARLIER ( 'Sheet1'[Region] ) ), 'Sheet1'[Count] ) RETURN Result

Measure:

zPrevYearCount3 = VAR PriorYear = MAX ( 'Sheet1'[Open Year] ) - 1 VAR Result = SUMX ( FILTER ( ALL ( Sheet1 ), 'Sheet1'[Open Year] = PriorYear && [Region] = SELECTEDVALUE ( 'Sheet1'[Region] ) ), 'Sheet1'[Count] ) RETURN Result

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: I need to show year over year values and they should be responsive to filters

jsgambel — Wed, 07 Dec 2022 14:47:38 GMT

Thanks for the reply Greg_Deckler

I had to make one small change to make it work:

ALLEXCEPT(Sheet1,Sheet1[Region]),

But, the result for this one is the same as the original.

Re: I need to show year over year values and they should be responsive to filters

Greg_Deckler — Wed, 07 Dec 2022 14:52:51 GMT

jsgambel It's hard to replicate with the given sample data since it only includes a single year.

Re: I need to show year over year values and they should be responsive to filters

jsgambel — Wed, 07 Dec 2022 15:13:01 GMT

Thanks v-zhangti . The measure worked for me. I'll use the column if/when I need it.