Custom column - Work rotation schedule

[Anonymous]

Hi Community, 

I need your help to get the work rotation schedule like in the example below. I have tried using a custom column in the query editor, but without luck. Any suggestions? 

[v-luwang-msft · Accepted solution]

Hi @Anonymous ，

If you use Matrix ,it with some limitations,and I suggest you to merge the column "off""on""start_date""end_date".

base table:

ON|OFF|START|END = TableB[On]&"|"&TableB[off] &"|"&TableB[start]&"|"&TableB[end]

Base on tableA create the following tables:

Tableon = CALCULATETABLE(TableA,FILTER(TableA,TableA[on/off]="on"))

Tableoff = CALCULATETABLE(TableA,FILTER(TableA,TableA[on/off]="off"))

Then use the below measure:

xvalue = 
IF(LOOKUPVALUE(Tableoff[Date],Tableoff[Date],MAX(TableB[Date]))<>blank(),"x",IF(LOOKUPVALUE(Tableon[Date],Tableon[Date],MAX(TableB[Date]))<>blank(),"-",BLANK()))

color = 
IF(LOOKUPVALUE(Tableon[Date],Tableon[Date],MAX(TableB[Date]))<>blank(),"Orange",BLANK())

Create matrix and set color:

Final output:

You could download my pbix file if you need!

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[amitchandak]

@Anonymous , you have try a approch like

Matrix as Project plan Visual: https://youtu.be/R25QoiyoSVs