Matrix

[HenryJS]

Hi all,

How can I get a matrix with Week Ending along columns but link to a table with two different date fields?

As below I linked the calendar table to 'MOT Expires' but this causes the 'Service Due' field to fall into the wrong date on the matrix.

Please see below example for GXZ 2918. 

I would like to have a matrix with week endings along columns and be able to drop in date fields from multiple fields.

Vehicle Reg	MOT expires	Service Due
DXZ 9642	21/01/2021	22/01/2021
EXZ 5946	25/06/2021	23/01/2021
EXZ 6637	02/06/2021	24/01/2021
EXZ 6638	11/06/2021	25/01/2021
EXZ 6639	25/10/2020	26/01/2021
EXZ 6640	03/06/2021	27/01/2021
GXZ 2918	27/09/2020	01/09/2020

[amitchandak]

@HenryJS , Have a month or week table with dates . No need to join

And use measures like this for these dates

MOT expires measure = minx(filter(table,[MOT expires] <=Date[Date] && [Service Due] >= Date[Date]),[MOT expires] )
Service Due measure = maxx(filter(table,[MOT expires] <=Date[Date] && [Service Due] >= Date[Date]),[Service Due] )

The week on column   and Vehicle Reg  on row

Calendar with week

https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Powe...

[HenryJS]

@amitchandak it did not work with the measure you sent - please see below

MOT expires measure = minx(filter('Vehicle Fleet Data',[MOT expires] <= 'calendar'[Date] && 'Vehicle Fleet Data'[Estimate Month Service Due] >= 'calendar'[Date]),'Vehicle Fleet Data'[MOT expires] )

[amitchandak]

@HenryJS , My mistake

Try like

MOT expires measure = minx(filter(table,table[MOT expires] <=max(Date[Date]) && table[Service Due] >= max(Date[Date])),table[MOT expires] )
Service Due measure = maxx(filter(table,table[MOT expires] <=max(Date[Date]) && table[Service Due] >= max(Date[Date])),table[Service Due] )

[HenryJS]

@amitchandak none of the dates appeared in the matrix!

please see below

[amitchandak · Accepted solution]

@HenryJS, see if this can solve the purpose