Forum Discussion
DAX Formula to calculate difference between previous week data
Hi,
Can anyone please tell me is following DAX formula is correct or wrong,
Previous Week = IF(ISBLANK([%GP]), BLANK(),
VAR CurrentWeek = SELECTEDVALUE('Calendar'[WeekNum])
VAR CurrentYear = SELECTEDVALUE('Calendar'[Year])
VAR MaxWeekNumber = CALCULATE(MAX('Calendar'[WeekNum]), ALL('Calendar'))
RETURN SUMX(FILTER(ALL('Calendar'),
IF(CurrentWeek = 1,
'Calendar'[WeekNum] = MaxWeekNumber && 'Calendar'[Year] = CurrentYear - 1,
'Calendar'[WeekNum] = CurrentWeek - 1 && 'Calendar'[Year] = CurrentYear)),
[%GP]))
%GP = (SUM(spend) - sum(pay)) / sum(spend)
%GP is in percentage format. Do above DAX formula giving the correct answer for difference between previous %GP data.
Please anyone can help me it would be great. I want to get the difference between previous week data. Can anyone giv me the answer to crete that measure.
Regards,
C
7 Replies
Hi cham ,
You may try to create measures like DAX below.
MaxWeekNumber = CALCULATE(MAX('Calendar'[WeekNum]), FILTER(ALL('Calendar'), 'Calendar'[Year]=MAX('Calendar'[Year))) Previous Week = VAR CurrentWeek = SELECTEDVALUE('Calendar'[WeekNum]) VAR CurrentYear = SELECTEDVALUE('Calendar'[Year]) VAR Filter=IF(CurrentWeek = 1, FILTER(ALL('Calendar'), 'Calendar'[WeekNum] =MAXX('Calendar', [MaxWeekNumber] ) && 'Calendar'[Year] = CurrentYear - 1), FILTER(ALL('Calendar'), 'Calendar'[WeekNum] =CurrentWeek - 1 && 'Calendar'[Year] = CurrentYear)) RETURN IF(ISBLANK([%GP]), BLANK(), SUMX(Filter, [%GP]))If I misunderstood it, could you please share your sample data and desired output screenshots for further analysis? You can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.
Best Regards,
Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi v-xicai
Please find the excel sheet,
Date Spend Pay %GP Sum %GP Difference between previous week %GP 13-07-19 125623.88 1891 0.999973913 14-07-19 2328891.88 16721 0.999974547 15-07-19 1231292.1 1011 0.999980154 2.999928614 - 22-07-19 112589.32 425.22 0.999980486 24-07-19 1233652.85 1833.72 0.999980629 21-07-19 122333659.5 2744.64 0.999981238 24-07-19 6252312.59 1726.67 0.999981396 24-07-19 7282519.59 347.25 0.999981963 24-07-19 2828221178 17955.15 0.999982039 24-07-19 2723016.45 3734.56 0.99356672 22-07-19 232130.75 1562.65 0.987814911 7.981269382 -4.981340768 31-07-19 234107.52 68.16 0.987229998 31-07-19 45565 45.5 0.985716413 31-07-19 551430.74 1078.24 0.985395205 31-07-19 222108.3 15534.32 0.980162897 3.938504512 4.04276487 01-08-19 1111011.23 10910.91 0.990179298 0.990179298 2.948325214
I want to get the difference between previous week %GP as I shown in Excel.The measure is giving me an error,
