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
jsgambel
Regular Visitor

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

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: 

Year1KeyTypeRegionCountDateYear
201020039423BreakawayEUROPE101/01/2010
201020048145StillNORTH AMERICA101/01/2010
201020069080StillEUROPE101/01/2010
201020199484AmbiantEUROPE101/01/2010
201020200079BreakawayEUROPE101/01/2010
201020200128AmbiantASIA101/01/2010
201020200213StillEUROPE101/01/2010
201020200417AmbiantASIA101/01/2010
201020200698GlassASIA101/01/2010
201020200700GlassAUSTRALIA101/01/2010
201020200728BreakawayEUROPE101/01/2010
201020201136BreakawayASIA101/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):

jsgambel_0-1670363752125.png

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:

jsgambel_2-1670363988192.png

 

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.

 

 

1 ACCEPTED SOLUTION
v-zhangti
Community Support
Community Support

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.

View solution in original post

5 REPLIES 5
v-zhangti
Community Support
Community Support

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.

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

Greg_Deckler
Super User
Super User

@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

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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.

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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