cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mrugankjoshi
Frequent Visitor

Percentage difference for displayed week and week before of the display week

Hello,

 

I have weekly data as below for 3 years period and trying to create a measure that gives me a percentage difference with the previous time period (for example if I put data by QTR/Month/year)

 

Input Data -

 

 image.png

 

 

 

 

 

 

 

 

 

 

Desired Output - 

 

image.png

 

 

 

 

 

 

Is there any way to create this measure dynamic that works if drilled on the Month/Quarter/Year level on the visual? I have a normal table as below with SUM in it but we want it to be replaced by the percentage difference.

 

image.png

 

1 ACCEPTED SOLUTION

Hi @Mrugankjoshi ,

 

Try this measure, Quarter, week, etc. are written similarly.

aa =
VAR _lastyear =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Month] ),
            YEAR ( [Date] )
                = YEAR ( MAX ( 'Table'[Date] ) ) - 1
        )
    )
VAR _thisyear =
    SUM ( 'Table'[Value] )
RETURN
    DIVIDE ( _thisyear - _lastyear, _lastyear )

 Screenshot 2021-04-13 163017.png

 

 

Best Regards,

Stephen Tao

 

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
VijayP
Super User I
Super User I

@Mrugankjoshi 

Just change the Previousweek Variable Measure to which is mentioned the video

 




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


Mrugankjoshi
Frequent Visitor

ok so I just got something after using ALLSELECTED in the filter but still, I'm not able to get the correct total at the bottom?image.png

 

 

 

 

image.png

VijayP
Super User I
Super User I

@Mrugankjoshi 

For Question 1 Watch this Video at 17:46 , where I have explained to see the week over week vairance analysis

https://www.youtube.com/watch?v=dLON3mj0los&list=PLWQB3PEUJKRmLf93pOxFQFIIe16ucwtW0&index=39

For the second question, is it possible to share some smaple data without any sensitive information




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


Thank you that is helpful.

Can we create any measure with percentage difference comparing all 3 years ( 2019,20,21) on Month, Week, Quarter, Year level? like below.

image.png

Hi @Mrugankjoshi ,

 

Try this measure, Quarter, week, etc. are written similarly.

aa =
VAR _lastyear =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Month] ),
            YEAR ( [Date] )
                = YEAR ( MAX ( 'Table'[Date] ) ) - 1
        )
    )
VAR _thisyear =
    SUM ( 'Table'[Value] )
RETURN
    DIVIDE ( _thisyear - _lastyear, _lastyear )

 Screenshot 2021-04-13 163017.png

 

 

Best Regards,

Stephen Tao

 

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors