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,1...
  • 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.

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

 

 

  • Chandrashekar's avatar
    Chandrashekar
    Resolver III
    4 years ago

    Hello,

     

    Purpose of this is to add dates in the slicer and sorry I did not mentioned earlier.

     

    I want to show working days in slicer. Example below

    • If today date then in slicer I need to show previous week dates(from 03-Jan to 07-Jan).
    • 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)

    Regards,

     

    Chandrashekar B