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

Calculating Week over Week Percent Change

Hi All,

 

I am trying to calculate week over week change percentage and display the data in a weekly format. I am usng the below measure code 

 

Week Over Week = 
VAR todaycost = CALCULATE(
    COUNTROWS(Requests),
    FILTER(Requests,Requests[Request Type] = "Escalation"),
    DATEADD('DateTable'[Date],0,DAY)
    )
   
VAR LastWeek =
    CALCULATE (
        COUNTROWS(Requests),
        FILTER(Requests,Requests[Request Type] = "Escalation"),
        DATEADD('DateTable'[Date],-7, DAY)
    )
RETURN
   DIVIDE(todaycost-LastWeek,LastWeek, 0)
   

 

This displays the week over week change for the latest data when put on a card. However, when I try and break it out by weeknumber, nothing shows. I have gotten down to the problem line being


"FILTER(Requests,Requests[Request Type] = "Escalation")".  - When this line is removed, it works as expected.

 

However, in my table I have a column of status's and I only want the week over week change for the "Escalation" status. 

 

Here is a picture of my date table 

 

 

2020-12-02 18_55_31-Clipboard.png

 

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

Ensure that to your visual, you drag Week of Year from the Date table.  Also, does this measure work?

Week Over Week = 
VAR todaycost = CALCULATE(
    COUNTROWS(Requests),
    Requests[Request Type] = "Escalation",
    DATEADD('DateTable'[Date],0,DAY)
    )
   
VAR LastWeek =
    CALCULATE (
        COUNTROWS(Requests),
        Requests[Request Type] = "Escalation",
        DATEADD('DateTable'[Date],-7, DAY)
    )
RETURN
   DIVIDE(todaycost-LastWeek,LastWeek, 0)

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

3 REPLIES 3
Ashish_Mathur
Super User
Super User

Hi,

Ensure that to your visual, you drag Week of Year from the Date table.  Also, does this measure work?

Week Over Week = 
VAR todaycost = CALCULATE(
    COUNTROWS(Requests),
    Requests[Request Type] = "Escalation",
    DATEADD('DateTable'[Date],0,DAY)
    )
   
VAR LastWeek =
    CALCULATE (
        COUNTROWS(Requests),
        Requests[Request Type] = "Escalation",
        DATEADD('DateTable'[Date],-7, DAY)
    )
RETURN
   DIVIDE(todaycost-LastWeek,LastWeek, 0)

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

I was using Week of Year from the date table, but what fixed it was removing "Filter" and just using "Requests =". Thankyou very much!

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.