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

[Nico_BE]

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.

[aduguid · Accepted solution]

1. Create "This Week" Measure:

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

1. Create "Last Week" Measure:

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

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
)

[aduguid · Accepted solution]

1. Create "This Week" Measure:

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

1. Create "Last Week" Measure:

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

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
)

[Nico_BE]

@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.

[Anonymous · Accepted solution]

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