Display last 4 weeks data based on selected week.

[nzharsh]

As you can see here in the screenshot, I've selected a week i.e 39 and my objective is to show data for the last 4 weeks from my selected week based on a calculated measure. One thing that needs to be remembered here is data for last 4 weeks is not in the table straightaway. The data for any selected week would be calculated from a measure.

[nzharsh]

Thanks for your guidance, Angelia.

[v-huizhn-msft · Accepted solution]

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