Forum Discussion
Matrix
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 |
@HenryJS, see if this can solve the purpose
5 Replies
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
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] )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] )
