Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to Solution.
@HenryJS, see if this can solve the purpose
@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
@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, see if this can solve the purpose