Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I need to calculate the change in qty between last 7 days and previous 7 days. I am doing that by:
Qty Change 7d = calculate([Returned Qty],DATESBETWEEN('DateTable'[Date],TODAY()-8,TODAY()) - calculate([Returned Qty],DATESBETWEEN('DateTable'[Date],TODAY()-7,TODAY()-15))
I am using a Date Dim table and I do have week names and week numbers in the table. How do I change the DAX to only include last 7 working days (and 7 working days prior to that) & exclude Saturday and Sunday?
Many Thanks.
Solved! Go to Solution.
Hi @Anonymous,
Based on my test, you should be able to follow steps below to get your expected.
1. Use the formula below to add a calculate column in your DateTable that is 1 for workdays and 0 for non-workdays.
IsWorkday = SWITCH(WEEKDAY('DateTable'[Date]),1,0,7,0,1)
2. User the formula below to create a measure to calculate the workdays between a date and Today.
Count Of Workday = COUNTROWS ( FILTER ( ALL ( 'DateTable' ), 'DateTable'[Date] <= TODAY () && 'DateTable'[Date] >= MAX ( 'DateTable'[Date] ) && 'DateTable'[IsWorkday] = 1 ) )
3. Then you should be able to use the formula below to create the measure to calculate the change in qty between last 7 days and previous 7 days.
Qty Change 7d = CALCULATE ( [Returned Qty], FILTER ( 'DateTable', [Count Of Workday] > 0 && [Count Of Workday] <= 8 ) ) - CALCULATE ( [Returned Qty], FILTER ( 'DateTable', [Count Of Workday] > 8 && [Count Of Workday] <= 15 ) )
Regards
Hi all, I've created a table showing the balance and two measures to show the rpevious days balance and the difference between the two balances.
I'm trying to create a measure which works out the net difference for the last 7 days. Could someone please help?
Hi @Anonymous,
Based on my test, you should be able to follow steps below to get your expected.
1. Use the formula below to add a calculate column in your DateTable that is 1 for workdays and 0 for non-workdays.
IsWorkday = SWITCH(WEEKDAY('DateTable'[Date]),1,0,7,0,1)
2. User the formula below to create a measure to calculate the workdays between a date and Today.
Count Of Workday = COUNTROWS ( FILTER ( ALL ( 'DateTable' ), 'DateTable'[Date] <= TODAY () && 'DateTable'[Date] >= MAX ( 'DateTable'[Date] ) && 'DateTable'[IsWorkday] = 1 ) )
3. Then you should be able to use the formula below to create the measure to calculate the change in qty between last 7 days and previous 7 days.
Qty Change 7d = CALCULATE ( [Returned Qty], FILTER ( 'DateTable', [Count Of Workday] > 0 && [Count Of Workday] <= 8 ) ) - CALCULATE ( [Returned Qty], FILTER ( 'DateTable', [Count Of Workday] > 8 && [Count Of Workday] <= 15 ) )
Regards
Awesome.. Thanks.
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |