Workday : Previous week

[Chandrashekar]

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 · Accepted solution]

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]

Hello,

Thank you 🙂

Regards,

Chandrashekar B

[amitchandak]

@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-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8

[Chandrashekar]

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]

@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]

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