Display last 4 weeks data based on selected week.

Hi nzharsh

Based on your description, you should create a measure to calculate the value for last 4 weeks? If it is, using measure is not able to achieve your target. Measures are based on standard aggregation functions, such as COUNT or SUM, or you can define your own formula by using DAX, which affects how you use them in a report. It only can be used as value level, rather than X or Y level. A Measure will return a value according to one row and column. It doesn’t return multiple values simultaneously.

From what I know, I try to reproduce the scenario and get expected result as follows.

First, create several columns using the formula below.

Week = CONCATENATE("WK",WEEKNUM(test1[Date]))
RANK = RANKX(test1,test1[Week],,ASC,Dense)

Then calculate the total sum based on Week column. Track the previous week’s result using LOOPUPVALUE function.

Count = CALCULATE(SUM(test1[Value]),ALLEXCEPT(test1,test1[Week]))
Last1Week = LOOKUPVALUE(test1[Count],test1[RANK],test1[RANK]-1)
Last2Week = LOOKUPVALUE(test1[Count],test1[RANK],test1[RANK]-2)
Last3Week = LOOKUPVALUE(test1[Count],test1[RANK],test1[RANK]-3)
Last4Week = LOOKUPVALUE(test1[Count],test1[RANK],test1[RANK]-4)

Finally, create a table, select the MachineID as axis, the Last1Week, Last2Week, Last3Week and Last4Week as Values level. You get the result shown in first picture. Add a slicer related with the table, it will show result for the last 4 weeks in different columns, please review the second screenshot.

Best Regards,
Angelia