Forum Discussion

Chandrashekar's avatar
Chandrashekar
Resolver III
4 years ago
Solved

Workday : Previous week

Hello Team,

 

Can you please help me with workday formula for below example.

If date_rec is Tuesday(11-Jan-2022) then workday should start from (04-Jan-2022,05-Jan-2022,06-Jan-2022,

07-Jan-2022,10-Jan-2022)

 

     

 

 

 

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi Chandrashekar ,

    According to your description, here's my solution.

    Create a new table by this formula.

    date period =
UNION (
    ADDCOLUMNS (
        DATESMTD ( 'date'[Date_rec] ),
        "Workday",
            IF (
                WEEKDAY ( 'date'[Date_rec], 2 ) IN { 1, 2, 3, 4, 5 },
                'date'[Date_rec] - 7,
                IF (
                    WEEKDAY ( 'date'[Date_rec], 2 ) = 6,
                    'date'[Date_rec] - 5,
                    'date'[Date_rec] - 6
                )
            )
    ),
    ADDCOLUMNS (
        DATESMTD ( 'date'[Date_rec] ),
        "Workday",
            IF (
                WEEKDAY ( 'date'[Date_rec], 2 ) IN { 1, 2, 3, 4 },
                'date'[Date_rec] - 6,
                IF (
                    WEEKDAY ( 'date'[Date_rec], 2 ) IN { 5, 6 },
                    'date'[Date_rec] - 4,
                    'date'[Date_rec] - 5
                )
            )
    ),
    ADDCOLUMNS (
        DATESMTD ( 'date'[Date_rec] ),
        "Workday",
            IF (
                WEEKDAY ( 'date'[Date_rec], 2 ) IN { 1, 2, 3 },
                'date'[Date_rec] - 5,
                IF (
                    WEEKDAY ( 'date'[Date_rec], 2 ) IN { 4, 5, 6 },
                    'date'[Date_rec] - 3,
                    'date'[Date_rec] - 4
                )
            )
    ),
    ADDCOLUMNS (
        DATESMTD ( 'date'[Date_rec] ),
        "Workday",
            IF (
                WEEKDAY ( 'date'[Date_rec], 2 ) IN { 3, 4, 5, 6 },
                'date'[Date_rec] - 2,
                IF (
                    WEEKDAY ( 'date'[Date_rec], 2 ) IN { 1, 2 },
                    'date'[Date_rec] - 4,
                    'date'[Date_rec] - 3
                )
            )
    ),
    ADDCOLUMNS (
        DATESMTD ( 'date'[Date_rec] ),
        "Workday",
            IF (
                WEEKDAY ( 'date'[Date_rec], 2 ) IN { 2, 3, 4, 5, 6 },
                'date'[Date_rec] - 1,
                IF (
                    WEEKDAY ( 'date'[Date_rec], 2 ) = 1,
                    'date'[Date_rec] - 3,
                    'date'[Date_rec] - 2
                )
            )
    )
)

    Get the expected result.

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

     

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

6 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Chandrashekar , WOW with work day filter

     

    new columns in date table

     


    new columns
    Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
    Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)

    Weekday = WEEKDAY([Date],2)
    Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
    OR
    Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

     

    and  measures
    This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank]) && 'Date'[Weekday] <6 ))
    Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1 && 'Date'[Weekday] <6 ))

     

    Power BI — Week on Week and WTD
    https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
    https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
    https://www.youtube.com/watch?v=pnAesWxYgJ8

    • Chandrashekar's avatar
      Chandrashekar
      Resolver III
      4 years ago

      Hello,

       

      I created new columns but need your help in creating measures(attached file) as am getting error.

      Sample PBX file 

      Regards,

      Chandrashekar B

      • amitchandak's avatar
        amitchandak
        Super User
        4 years ago

        Chandrashekar , You do have a Qty column in the table. Second All was on Date table not on 'Date'[Date]. Thrid you have not created date table

         

         

        This has no error, but still not as I suggested

         CALCULATE(COUNTROWS('Table'), FILTER(ALL('Table'),'table'[Week Rank] =max('Table'[Week Rank]) && 'Table'[Weekday] <6 ))

         

         

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    4 years ago

    Hi Chandrashekar ,

    According to your description, here's my solution.

    Create a new table by this formula.

    date period =
UNION (
    ADDCOLUMNS (
        DATESMTD ( 'date'[Date_rec] ),
        "Workday",
            IF (
                WEEKDAY ( 'date'[Date_rec], 2 ) IN { 1, 2, 3, 4, 5 },
                'date'[Date_rec] - 7,
                IF (
                    WEEKDAY ( 'date'[Date_rec], 2 ) = 6,
                    'date'[Date_rec] - 5,
                    'date'[Date_rec] - 6
                )
            )
    ),
    ADDCOLUMNS (
        DATESMTD ( 'date'[Date_rec] ),
        "Workday",
            IF (
                WEEKDAY ( 'date'[Date_rec], 2 ) IN { 1, 2, 3, 4 },
                'date'[Date_rec] - 6,
                IF (
                    WEEKDAY ( 'date'[Date_rec], 2 ) IN { 5, 6 },
                    'date'[Date_rec] - 4,
                    'date'[Date_rec] - 5
                )
            )
    ),
    ADDCOLUMNS (
        DATESMTD ( 'date'[Date_rec] ),
        "Workday",
            IF (
                WEEKDAY ( 'date'[Date_rec], 2 ) IN { 1, 2, 3 },
                'date'[Date_rec] - 5,
                IF (
                    WEEKDAY ( 'date'[Date_rec], 2 ) IN { 4, 5, 6 },
                    'date'[Date_rec] - 3,
                    'date'[Date_rec] - 4
                )
            )
    ),
    ADDCOLUMNS (
        DATESMTD ( 'date'[Date_rec] ),
        "Workday",
            IF (
                WEEKDAY ( 'date'[Date_rec], 2 ) IN { 3, 4, 5, 6 },
                'date'[Date_rec] - 2,
                IF (
                    WEEKDAY ( 'date'[Date_rec], 2 ) IN { 1, 2 },
                    'date'[Date_rec] - 4,
                    'date'[Date_rec] - 3
                )
            )
    ),
    ADDCOLUMNS (
        DATESMTD ( 'date'[Date_rec] ),
        "Workday",
            IF (
                WEEKDAY ( 'date'[Date_rec], 2 ) IN { 2, 3, 4, 5, 6 },
                'date'[Date_rec] - 1,
                IF (
                    WEEKDAY ( 'date'[Date_rec], 2 ) = 1,
                    'date'[Date_rec] - 3,
                    'date'[Date_rec] - 2
                )
            )
    )
)

    Get the expected result.

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

     

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