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

[Anonymous]

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-

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.

[v-xiaotang]

Hi @Anonymous 

I just want to confirm if you resolved this issue? If yes, you can accept the answer helpful as the solution or share you method and accept it as solution, thanks for your contribution to improve Power BI.

If you need more help, please let me know.

Best Regards,

Community Support Team _Tang

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

[amitchandak]

@Anonymous , With help from a date table and week Rank

new columns
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

measures
This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

or 7 days behind measure

7 behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-7,DAy))

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8