Display this week's data with last week's data on matrix table.

[matrix_user]

Hello,

I have a matrix table which displays in a column, current seats (OccupiedSeatsTW) available but I also want to include another column dislaying last week's seat availability (OccupiedSeatsLW). When I apply a measure, it does not bring up any results:

1)For OccupiedSeatsTW (this week),  I am using a measure:  OccupiedSeatsTW = DIVIDE([SumOfTakenSeats],[ActiveSeats]).

   For OccupiedSeatsLW (last week) I am using a measure I saw on YouTube:

OccupiedSeatsLW =

VAR CurrentWeek = SELECTEDVALUE ( 'Calendar'[Week Number] )

VAR CurrentYear = SELECTEDVALUE ( 'Calendar'[Year] )

VAR MaxWeekNumber = CALCULATE ( MAX ( 'Calendar'[Week Number] ), ALL ( 'Calendar' ) )

RETURN

SUMX (

FILTER (ALL ('Calendar'),

IF (CurrentWeek = 1,

'Calendar'[Week Number] = MaxWeekNumber && 'Calendar'[Year] = CurrentYear -1,

'Calendar'[Week Number] = CurrentWeek -1 && 'Calendar'[Year] = CurrentYear)),

[ActiveSeats])

 The catch is, that my reporting weeks start on Wednesdays and ends on Tuesday;

2) My Calendar table (I prefer to write my own tables instead of importing them), and so they consist of the following:

3) My Source(Fact) table consist of the following and has dates from 03/09/21 tp 16/09/2021:

4) Measures used to extract results into the matrix for current and last week as follows (a couple of these measures are mentioned in point 1 above:

Is there anyone out there who can help me display OccupiedSeatsLW (for last week) on this matrix?

Cheers,

[v-robertq-msft · Accepted solution]

Hi, 

According to your description, I can roughly understand your requirement, I think you can achieve this using two measures and a calculated column, you can try my steps:

This is the test data I created based on your sample pictures:

Create a calculated column in the main table:

Weeknum = WEEKNUM('Table'[Date],2)

Then create two measures like this:

OccupiedSeats LW =

CALCULATE(SUM('Table'[OccupiedSeats]),FILTER(ALL('Table'),[Weeknum]=WEEKNUM(TODAY())-1))

OccupiedSeats TW =

CALCULATE(SUM('Table'[OccupiedSeats]),FILTER(ALL('Table'),[Weeknum]=WEEKNUM(TODAY())))

And you can create a table chart to palce the two measures to get what you want, like this:

You can download my test pbix file below

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[matrix_user]

Thank you Robert. Much appreciated.

[AlexisOlson]

Two problems here.

First OP is using Tue - Wed weeks, so you want WEEKNUM ( 'Table'[Date], 13 ).

The bigger issue is that your DAX won't work for the first week of a year.

If you create a week offset index that doesn't reset across years, then this approach should work fine.

[matrix_user]

Hi Alexis,

Yes I have read a few articles about possible issues when the new year starts. Thank you.

[matrix_user]

What I am hoping to get is a matrix that shows last week's period (3/11/2021 to 9/11/2021) in the first column and this week's period (10/11/2021 to 16/11/2021) in the second period.

The end result will also be just the total as below:

Can anyone guide me?

[AlexisOlson]

How about something much simpler like this?

OccupiedSeatsLW =
CALCULATE ( [OccupiedSeatsTW], DATEADD ( 'Calendar'[Date], -7, DAY ) )