CODEWITHSUNDEEP
powerbidaxpowerbi-desktop
Chicken Sandwich No Pickles
Chicken Sandwich No Pickles

Reputation: 3362

DAX Based on Previous Week

I have table_a in SSMS that looks like this:

REPORT_DATE     product       sales     
2021-08-01      prod_a        $100
2021-08-01      prod_b        $200
2021-07-25      prod_a        $200
2021-07-25      prod_b        $50
2021-07-19      prod_a        $20
2021-07-19      prod_b        $60

I want to create a DAX measure that finds the % difference this week to last week and then ranks it based on what threshold it is at.

Here is the Psuedo code for it:

(Sales This Week / Sales Last Week) = X%

If X > 0 then "Good"
If X = 0 then "Neutral"
If X < 0 then "Bad"

How can I do this using DAX? I am not sure how to grab a previous week's values.

Upvotes: 0

Views: 183

Answers (1)

AmilaMGunawardana
AmilaMGunawardana

Reputation: 1830

Maybe here I have some syntax errors since I don't have your power bi file.I just got the max date from the table and differentiated it into 14 days before and 7 days before the date to get the previous week vice versa for this week. Please for my code below for the calculation. Feel free to add another IF statement for X=0.

var maxdate = calculate(MAX(table_a[Date]))
var day14back = DATEADD(maxdate,-14,day)
var day7back = DATEADD(maxdate,-7,day)

var priv_weeksales = CALCULATE(sum(table_a[sales]),table_a[Date]<=day7back,table_a[Date]>=day14back)
var this_weeksales = CALCULATE(sum(table_a[sales]),table_a[Date]<=maxdate,table_a[Date]>day7back)

var X = DIVIDE(this_weeksales,priv_weeksales,0)

return IF(X>0,"Good",IF(X<0,"Bad","Neutral"))

Upvotes: 2

Related Questions