topic Re: Workday : Previous week in DAX Commands and Tips

Workday : Previous week

Chandrashekar — Mon, 10 Jan 2022 09:19:33 GMT

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)

Re: Workday : Previous week

amitchandak — Mon, 10 Jan 2022 09:49:18 GMT

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

Re: Workday : Previous week

Chandrashekar — Mon, 10 Jan 2022 10:15:23 GMT

Hello,

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

Sample PBX file

Regards,

Chandrashekar B

Re: Workday : Previous week

amitchandak — Mon, 10 Jan 2022 11:44:38 GMT

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

Re: Workday : Previous week

Chandrashekar — Mon, 10 Jan 2022 11:52:01 GMT

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

Re: Workday : Previous week

v-yanjiang-msft — Thu, 13 Jan 2022 06:32:25 GMT

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.

Re: Workday : Previous week

Chandrashekar — Thu, 13 Jan 2022 06:55:00 GMT

Hello,

Thank you 🙂

Regards,

Chandrashekar B