Forum Discussion

psh_unreal's avatar
psh_unreal
Regular Visitor
3 years ago
Solved

Gantt - Martix - Rostering Work Patterns

Hi Power BI Community,   I am new to Power BI and have been tasked with creating a Rostering Dashboard within Power BI. I have used the Matrix approach and have measures configured along with condi...
Need Help
Tutorial Requests
  • v-yinliw-msft's avatar
    v-yinliw-msft
    3 years ago

    Hi psh_unreal,

     

    You can try this method:

    New a calendar table:

    Date = CALENDAR(MIN('Table'[Start]),MAX('Table'[End]))

    Then calculate the weekday.

    New a column:

    Weekday = WEEKDAY('Date'[Date],2)

    Then new a measure in the sample table:

    LeaveDays =
SWITCH (
    TRUE (),
    SELECTEDVALUE ( 'Date'[Weekday] ) = 1
        && CALCULATE (
            MAX ( 'Table'[Monday] ) = 1,
            FILTER ( 'Table', 'Table'[EmployeeID] )
        ), "Mon leave",
    SELECTEDVALUE ( 'Date'[Weekday] ) = 2
        && CALCULATE (
            MAX ( 'Table'[Tuesday] ) = 1,
            FILTER ( 'Table', 'Table'[EmployeeID] )
        ), "Tue leave",
    SELECTEDVALUE ( 'Date'[Weekday] ) = 3
        && CALCULATE (
            MAX ( 'Table'[Wednesday] ) = 1,
            FILTER ( 'Table', 'Table'[EmployeeID] )
        ), "Wed leave",
    SELECTEDVALUE ( 'Date'[Weekday] ) = 4
        && CALCULATE (
            MAX ( 'Table'[Thursday] ) = 1,
            FILTER ( 'Table', 'Table'[EmployeeID] )
        ), "Thu leave",
    SELECTEDVALUE ( 'Date'[Weekday] ) = 5
        && CALCULATE (
            MAX ( 'Table'[Friday] ) = 1,
            FILTER ( 'Table', 'Table'[EmployeeID] )
        ), "Fri leave",
    SELECTEDVALUE ( 'Date'[Weekday] ) = 6
        && CALCULATE (
            MAX ( 'Table'[Saturday] ) = 1,
            FILTER ( 'Table', 'Table'[EmployeeID] )
        ), "Sat leave",
    SELECTEDVALUE ( 'Date'[Weekday] ) = 7
        && CALCULATE (
            MAX ( 'Table'[Sunday] ) = 1,
            FILTER ( 'Table', 'Table'[EmployeeID] )
        ), "Sun leave"
)

     

    You can use a slicer and the result is:

     

     

     

    Hope this helps you.

    Here is my PBIX file.

     

    Best Regards,

    Community Support Team _Yinliw

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.