cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AliceW
Post Prodigy
Post Prodigy

Calculate the difference between one month and the previous selected month

Hello ladies and gents,

This is killing me. I have an Amount for each month of the previous two years. I want to calculate the difference between one month and the previously SELECTED month.

Like say the user selects January 2021 and January 2020. The difference should be between these months.

But if the user selects January and February 2021, the difference should be between them.

If the user selects January and June 2021, you guessed it, the difference should be between them.

I know we have PREVIOUSMONTH and SAMEPERIODLAST YEAR. But is there a way to mix them?

Huge thanks,

Alice

 

2 ACCEPTED SOLUTIONS
KNP
Super User
Super User

Hi Alice,

 

I may be misunderstanding, but doesn't SAMEPERIODLASTYEAR do this by itself?

When you say...

KNP_0-1642970456626.png

Does that mean if the user selects January and June 2021, you want to compare it with January and June 2020?

 

Like I say, I may be misunderstanding, but I've attached a sample PBIX in case it helps.

Gif below demonstrates what I mean.

KNP_1-1642970604585.gif

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

View solution in original post

V-lianl-msft
Community Support
Community Support

Hi @AliceW ,

 

Maybe I understand,Please try this measure and refer to the attached pbix.

 

diff = 
VAR month_1 =
    CALCULATE (
        TOPN ( 1, VALUES ( 'Table'[year_month] ), 'Table'[year_month], ASC ),
        ALLSELECTED ( 'Table' )
    )
VAR month_2 =
    CALCULATE (
        TOPN ( 1, VALUES ( 'Table'[year_month] ), 'Table'[year_month], DESC ),
        ALLSELECTED ( 'Table' )
    )
VAR First_ =
    CALCULATE (
        SUM ( 'Table'[value] ),
        FILTER ( 'Table', 'Table'[year_month] = month_1 )
    )
VAR Second_ =
    CALCULATE (
         SUM ( 'Table'[value] ),
        FILTER ( 'Table', 'Table'[year_month] = month_2 )
    )
RETURN
    Second_-First_

 

Vlianlmsft_0-1643163190816.pngVlianlmsft_1-1643163210275.png

 

 

Best Regards,
Liang
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

3 REPLIES 3
AliceW
Post Prodigy
Post Prodigy

Thank you so much, guys!!

V-lianl-msft
Community Support
Community Support

Hi @AliceW ,

 

Maybe I understand,Please try this measure and refer to the attached pbix.

 

diff = 
VAR month_1 =
    CALCULATE (
        TOPN ( 1, VALUES ( 'Table'[year_month] ), 'Table'[year_month], ASC ),
        ALLSELECTED ( 'Table' )
    )
VAR month_2 =
    CALCULATE (
        TOPN ( 1, VALUES ( 'Table'[year_month] ), 'Table'[year_month], DESC ),
        ALLSELECTED ( 'Table' )
    )
VAR First_ =
    CALCULATE (
        SUM ( 'Table'[value] ),
        FILTER ( 'Table', 'Table'[year_month] = month_1 )
    )
VAR Second_ =
    CALCULATE (
         SUM ( 'Table'[value] ),
        FILTER ( 'Table', 'Table'[year_month] = month_2 )
    )
RETURN
    Second_-First_

 

Vlianlmsft_0-1643163190816.pngVlianlmsft_1-1643163210275.png

 

 

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

KNP
Super User
Super User

Hi Alice,

 

I may be misunderstanding, but doesn't SAMEPERIODLASTYEAR do this by itself?

When you say...

KNP_0-1642970456626.png

Does that mean if the user selects January and June 2021, you want to compare it with January and June 2020?

 

Like I say, I may be misunderstanding, but I've attached a sample PBIX in case it helps.

Gif below demonstrates what I mean.

KNP_1-1642970604585.gif

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!