Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I need to shade/conditional format the days of the week based on start and end date as below.
Scheduled Start date and scheduled End date are calculated columns generting dates that exclude weekends.
Solved! Go to Solution.
Hi @Anonymous ,
Regarding your questions you can do the following:
More than 1 week
Create a measure:
Number of days = SUMX('Table';DATEDIFF('Table'[Schedule Start date];'Table'[Schedule End date];DAY)) + 1
Adjust your previous measure to:
Day of the week calculation =
IF (
[Number of days] > 7;
1;
IF (
WEEKDAY ( MAX ( 'Table'[Schedule Start Date] ); 1 ) > MAX ( 'Weekdays'[ID] )
|| WEEKDAY ( MAX ( 'Table'[Schedule End Date] ); 1 ) < MAX ( 'Weekdays'[ID] );
0;
1
)
)
Assuming you want to have all days ocupied correct?
Making use of a table
Instead of making a measure make one for each of the weekdays and place them on your table then use the same condittional formatting.
Monday =
IF (
[Number of days] > 7;
1;
IF (
WEEKDAY ( MAX ( 'Table'[Schedule Start Date] ); 1 ) > 2
|| WEEKDAY ( MAX ( 'Table'[Schedule End Date] ); 1 ) < 2;
0;
1
)
)
Tuesday =
IF (
[Number of days] > 7;
1;
IF (
WEEKDAY ( MAX ( 'Table'[Schedule Start Date] ); 1 ) > 3
|| WEEKDAY ( MAX ( 'Table'[Schedule End Date] ); 1 ) < 3;
0;
1
)
)
Wednesday =
IF (
[Number of days] > 7;
1;
IF (
WEEKDAY ( MAX ( 'Table'[Schedule Start Date] ); 1 ) > 4
|| WEEKDAY ( MAX ( 'Table'[Schedule End Date] ); 1 ) < 4;
0;
1
)
)
Thursday =
IF (
[Number of days] > 7;
1;
IF (
WEEKDAY ( MAX ( 'Table'[Schedule Start Date] ); 1 ) > 5
|| WEEKDAY ( MAX ( 'Table'[Schedule End Date] ); 1 ) < 5;
0;
1
)
)
Friday =
IF (
[Number of days] > 7;
1;
IF (
WEEKDAY ( MAX ( 'Table'[Schedule Start Date] ); 1 ) > 6
|| WEEKDAY ( MAX ( 'Table'[Schedule End Date] ); 1 ) < 6;
0;
1
)
)
Check PBIX file attach with both options.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @Anonymous ,
Create a table with the days of the week (don't include the weekends):
ID Day of the week
2 | Monday |
3 | Tuesday |
4 | Wednesday |
5 | Thursday |
6 | Friday |
Now add the following measure to your model:
Day of the week calculation =
IF (
WEEKDAY ( MAX ( 'Table'[Schedule Start Date] ); 1 ) > MAX ( 'Week Days'[ID] )
|| WEEKDAY ( MAX ( 'Table'[Schedule End Date] ); 1 ) < MAX ( 'Week Days'[ID] );
0;
1
)
Now construct the following matrix:
Now add conditional formatting for 1 (black) and 0 (white) on the background and font color.
Result attach.
Please be aware that looking at your data you do not have value that go more than 2 or 3 days if difference of dates is bigger than 1 week you my need to make some adjustments.
Regards.
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix ,
Thanks for this great help.
The solution seems very close. I have incorporated the solution in the files below.
As you guessed it right, We do have items that run over a week, so i would need your help to capture this scenario as well.
Is there a way to achieve this result in a calculated column and table view instead of matrix view as it could be convenient??
We would like to view a line item as a seperate row without any grouping, whereas matrix groups rows if i am not wrong.
Hi @Anonymous ,
Regarding your questions you can do the following:
More than 1 week
Create a measure:
Number of days = SUMX('Table';DATEDIFF('Table'[Schedule Start date];'Table'[Schedule End date];DAY)) + 1
Adjust your previous measure to:
Day of the week calculation =
IF (
[Number of days] > 7;
1;
IF (
WEEKDAY ( MAX ( 'Table'[Schedule Start Date] ); 1 ) > MAX ( 'Weekdays'[ID] )
|| WEEKDAY ( MAX ( 'Table'[Schedule End Date] ); 1 ) < MAX ( 'Weekdays'[ID] );
0;
1
)
)
Assuming you want to have all days ocupied correct?
Making use of a table
Instead of making a measure make one for each of the weekdays and place them on your table then use the same condittional formatting.
Monday =
IF (
[Number of days] > 7;
1;
IF (
WEEKDAY ( MAX ( 'Table'[Schedule Start Date] ); 1 ) > 2
|| WEEKDAY ( MAX ( 'Table'[Schedule End Date] ); 1 ) < 2;
0;
1
)
)
Tuesday =
IF (
[Number of days] > 7;
1;
IF (
WEEKDAY ( MAX ( 'Table'[Schedule Start Date] ); 1 ) > 3
|| WEEKDAY ( MAX ( 'Table'[Schedule End Date] ); 1 ) < 3;
0;
1
)
)
Wednesday =
IF (
[Number of days] > 7;
1;
IF (
WEEKDAY ( MAX ( 'Table'[Schedule Start Date] ); 1 ) > 4
|| WEEKDAY ( MAX ( 'Table'[Schedule End Date] ); 1 ) < 4;
0;
1
)
)
Thursday =
IF (
[Number of days] > 7;
1;
IF (
WEEKDAY ( MAX ( 'Table'[Schedule Start Date] ); 1 ) > 5
|| WEEKDAY ( MAX ( 'Table'[Schedule End Date] ); 1 ) < 5;
0;
1
)
)
Friday =
IF (
[Number of days] > 7;
1;
IF (
WEEKDAY ( MAX ( 'Table'[Schedule Start Date] ); 1 ) > 6
|| WEEKDAY ( MAX ( 'Table'[Schedule End Date] ); 1 ) < 6;
0;
1
)
)
Check PBIX file attach with both options.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @TomMartens,
Thanks for looking into this.
I am sharing the actual powerbi and the dataset in the background with you.
I have tried the solutions shared by MFelix , they seem to be very close except that the users wouldl like to use a table visual and also we have items that go beyond a week which you will find in the actual files.
Is there a way to achieve the results in calcualted column?
Hey @Anonymous ,
please provide a pbix file that contains sample data, but still represents your data model, upload the file to onedrive or dropbox and share the link. If you use an Excel file to create the sample data, then also upload the xlsx and share the link as well.
Regards,
Tom
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |