topic Re: calculate current week data using current week and previous week info in DAX in DAX Commands and Tips

calculate current week data using current week and previous week info in DAX

Anonymous — Mon, 30 May 2022 10:24:56 GMT

Hi,

I hope everyone's well.

I am trying to create a measure that uses previous week and current week data. The table looks like the following-

week_end	profit	loss	reference_for_1_may	net_product_margin
29/05/2022	308	640		339779
22/05/2022	576	1366		340111
15/05/2022	658	1494		340901
08/05/2022	600	1198		341737
01/05/2022	612	1794	342335	342335

I am trying to create a measure net_product_margin which is calculated by using last week's net_product_margin value - current week 'loss' + current week 'gain'. Here's the code that I tried but it doesn't work perfectly. This does calculate the net_product_margin value correctly for 8th May 2022 but since I am using the column reference_for_1_may it doesn't calculate the later week net_product_margin value correctly.

net_product_margin = var _lastweek = CALCULATE('All Measures'[reference_for_1_may], FILTER(ALL('Dim_Date'),'Dim_Date'[End_of_Week] = SELECTEDVALUE(Dim_Date[End_of_Week]) -7 )) RETURN _lastweek - 'All Measures'[loss] + 'All Measures'[profit]

This is how the formula looks in excel.

I have the data from 1st May 2022 in my table so to have a reference I have added a reference column called reference_for_1_may that acts as reference data to calculate later week net_product_margin. I am scratching my head on this. Any help would be much appreciated.

Thank you.

Re: calculate current week data using current week and previous week info in DAX

tamerj1 — Mon, 30 May 2022 13:42:43 GMT

Hi Anonymous
Here is the sample file with the solution https://www.dropbox.com/t/6o38jT13uTKnx2Fh

New Column

net_product_margin_calculated = VAR ReferenceValue = 342335 VAR FirstDateEver = MIN ( 'P&L'[week_end] ) RETURN SUMX ( FILTER ( 'P&L', 'P&L'[week_end] <= EARLIER ( 'P&L'[week_end] ) && 'P&L'[week_end] > FirstDateEver ), 'P&L'[profit] - 'P&L'[loss] ) + ReferenceValue

Re: calculate current week data using current week and previous week info in DAX

Anonymous — Mon, 30 May 2022 14:31:23 GMT

Hi tamerj1

Thank you so much for the response. I am trying to replicate this but getting an error with EARLIER function.

Re: calculate current week data using current week and previous week info in DAX

tamerj1 — Mon, 30 May 2022 14:32:48 GMT

Anonymous

The solution is based on a calculated column not a measure