Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 @Anonymous ,

 

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

@Anonymous 

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!


Anonymous
Not applicable

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

@Anonymous 

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!


Anonymous
Not applicable

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 @Anonymous ,

 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.