topic Re: Create a measure to sum this week and divide by last value from last week in DAX Commands and Tips

Create a measure to sum this week and divide by last value from last week

ashrafkotb — Mon, 11 Jan 2021 17:32:46 GMT

Hello,

I have a table that looks somehow like this:

Date	Portfolio Size	P/L
08/01/2021	$100,000	$100
11/01/2021	$100,100	$200
12/01/2021	$100,300	$300
13/01/2021	$100,600	$200

08/01/2021 is the last working day of the last week.

I would like to create a measure that will calculate the sum of P/L for the current week and divide by the last "Portfolio Size" value from last week.

Thank you.

Re: Create a measure to sum this week and divide by last value from last week

amitchandak — Tue, 12 Jan 2021 02:02:23 GMT

ashrafkotb , first if all you need to create a Saturday to Friday Week (Based on date you Said)

Refer my blog for this - Any Weekday Week - Start From Any day of Week
https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Power-BI-Turning/ba-p/1187482

Now you have to create a Rank on the Week start date or Week Number and use that, refer to my blog for that

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

The column you need in the date table

Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)

or
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYMM format

Measures you need
This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

Re: Create a measure to sum this week and divide by last value from last week

ashrafkotb — Tue, 12 Jan 2021 13:34:52 GMT

Thank you, Amit. The week calculations were very helpful.
I created all the week columns and they make sense. I'm still not clear about the measure.

The measure I'm looking for should sum the (P/L) column and divide it by the last value of the (Portfolio Size) column from the previous week.

Appreciate if you could help with the correct measure calculation.

Thank you

Re: Create a measure to sum this week and divide by last value from last week

ashrafkotb — Tue, 12 Jan 2021 15:51:08 GMT

Using this measure:

Last portfolio = CALCULATE(LASTNONBLANK('tablename'[Portfolio Size],1),FILTER(('tablename'),'tablename'[Week of Year]=MAX('tablename'[Week of Year])-1))

Returns the first value of the portfolio size in the previous week, rather than returning the last value. Any clue how to return the actual last value?