DAX Formula to calculate difference between previous week data

[cham]

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

[v-xicai]

Hi @cham  ,

Does that make sense? If so, kindly mark my answer as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best regards

Amy

[cham]

Hi @v-xicai ,

Its giving me the same error,

[v-xicai]

Hi @cham ,

Please check your formula carefully, the red parts in following places could be same. The second one refers to the first one.

Best Regards,

Amy

[cham]

Hi @v-xicai 

I follow the same measure as you written. But Its giving me an error.

Regards,

Cham

[v-xicai]

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.

[cham]

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,

[v-xicai]

Hi @cham ,

You may change the red part to "_filter", and try it again.

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.