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

[jsgambel]

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.

[v-zhangti · Accepted solution]

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.

[jsgambel]

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

[Greg_Deckler]

@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

[jsgambel]

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.

[Greg_Deckler]

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