Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Conditional format based on start and end date

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 ...
Need Help
  • MFelix's avatar
    MFelix
    6 years ago

    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

MFelix's avatar
MFelix
Super User
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

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's avatar
    MFelix
    Super User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      MFelix 

       

      Thanks for the solution. It is brilliant