topic Re: Get this week, last week and average last 4 weeks data based on selected week in slicer in DAX Commands and Tips

Get this week, last week and average last 4 weeks data based on selected week in slicer

Nico_BE — Mon, 24 Jun 2024 12:50:51 GMT

Hi all,

I am very new to Power BI and hope that someone can answer my question.

This is the kind of data that is imported and this data will always start on the first of january untill the 31st december:

I would like to create a matrix (or something else?) as followed, based on the week that is selected in a slicer.:

So the goal is that when in the slicer "5" is selected (week 5), it will give the data of week 5 in "This week", the data of week 4 in "Last week" and the average of week 1 until 4 in "Last 4 weeks Avg".

This would than automaticly change when another week is selected in the slicer.

I hope this is clear as what I would like to achieve.

Re: Get this week, last week and average last 4 weeks data based on selected week in slicer

aduguid — Mon, 24 Jun 2024 13:01:21 GMT

Create "This Week" Measure:

This Week = VAR SelectedWeek = MAX('YourTable'[WeekNumber]) RETURN CALCULATE( SUM('YourTable'[YourMetric]), 'YourTable'[WeekNumber] = SelectedWeek )

Create "Last Week" Measure:

Last Week = VAR SelectedWeek = MAX('YourTable'[WeekNumber]) RETURN CALCULATE( SUM('YourTable'[YourMetric]), 'YourTable'[WeekNumber] = SelectedWeek - 1 )

Create "Last 4 Weeks Avg" Measure:

Last 4 Weeks Avg = VAR SelectedWeek = MAX('YourTable'[WeekNumber]) RETURN CALCULATE( AVERAGE('YourTable'[YourMetric]), 'YourTable'[WeekNumber] >= SelectedWeek - 4 && 'YourTable'[WeekNumber] < SelectedWeek )

Re: Get this week, last week and average last 4 weeks data based on selected week in slicer

Nico_BE — Mon, 24 Jun 2024 13:22:45 GMT

aduguid Thanks. This already helps me out, but now I can't seem to make it work to have in the colums the week, last week and average and to have as rows the description of each value.

Re: Get this week, last week and average last 4 weeks data based on selected week in slicer

Anonymous — Tue, 25 Jun 2024 05:31:06 GMT

Hi aduguid ,Thanks for your quick reply, I will add more.

Hi Nico_BE ,

Do you want to create a matrix similar to the picture?Like this?

The Table data is shown below:

Please follow these steps:

1.Enter the following data manually.

2.Use the following DAX expression to create measures

This week = VAR _axtualOrPlan = SELECTEDVALUE(Table2[Row]) VAR _weeknumber = MAX('Table'[Week]) RETURN SWITCH(TRUE(), _axtualOrPlan = "DL Actual",SUMX(FILTER('Table','Table'[Week] = _weeknumber),[DL Actual]), _axtualOrPlan = "DL Plan",SUMX(FILTER('Table','Table'[Week] = _weeknumber),[DL Plan]) )

Last week = VAR _axtualOrPlan = SELECTEDVALUE(Table2[Row]) VAR _weeknumber = MAX('Table'[Week]) RETURN SWITCH(TRUE(), _axtualOrPlan = "DL Actual",CALCULATE(SUMX(FILTER('Table','Table'[Week] = _weeknumber - 1),[DL Actual]),ALL('Table'[Week])), _axtualOrPlan = "DL Plan",CALCULATE(SUMX(FILTER('Table','Table'[Week] = _weeknumber - 1),[DL Plan]),ALL('Table'[Week])) )

Last 4 week av = VAR _axtualOrPlan = SELECTEDVALUE(Table2[Row]) VAR _weeknumber = MAX('Table'[Week]) RETURN SWITCH(TRUE(), _axtualOrPlan = "DL Actual",CALCULATE(AVERAGEX(FILTER('Table','Table'[Week] >= _weeknumber - 4 && 'Table'[Week] < _weeknumber),[DL Actual]),ALL('Table'[Week])), _axtualOrPlan = "DL Plan",CALCULATE(AVERAGEX(FILTER('Table','Table'[Week] >= _weeknumber - 4 && 'Table'[Week] < _weeknumber),[DL Plan]),ALL('Table'[Week])) )

3.Final output