Conditional format based on start and end date

[Anonymous]

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.

[MFelix]

Hi @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:

• Rows:
  • Batch
  • Start Date
  • End Date
  • ....
  • Other Columns
• Columns:
  • Day of the week (previous create table)
• Values
  • Measure [Day of the week calculation]

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

[Anonymous]

Hi @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.

PBI File

Excel dataset

[MFelix · Accepted 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

[Anonymous]

@MFelix 

Thanks for the solution. It is brilliant

[Anonymous]

Hi @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?

PBI file 

Excel file

[TomMartens]

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