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 weekends.

 

 

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

6 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    6 years ago

    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

  • 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 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

    • 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