topic Re: Count Rows based on Previous WeekDay in DAX Commands and Tips

Count Rows based on Previous WeekDay

Sudhakar510 — Fri, 21 Oct 2022 22:30:21 GMT

Hi I have Date column in a Table1

If Today is Monday then I want to count rows for last Friday only which is for last weekday.

and another condition is

If Monday then I want to count rows till last Thursday and Earlier

Or If Tuesday then I want to count rows till last Friday and Earlier.

Please any help? Basically I am trying for 2 rules

Last week day count

Last before weekday count and earlier

Re: Count Rows based on Previous WeekDay

amitchandak — Sat, 22 Oct 2022 03:58:15 GMT

Sudhakar510 , Few columns you need in you date table

Work Day = if(WEEKDAY([Date],2)>=6,0,1)
Work Date = if(WEEKDAY([Date],2)>=6,BLANK(),[Date])
Work Date Cont = if([Work Day]=0,maxx(FILTER('Date',[Date]<EARLIER([Date]) && [Work Day]<> EARLIER([Work Day]) ),[Date]),[Date])
Work Date cont Rank = RANKX(ALL('Date'),[Work Date Cont],,ASC,Dense)
Work day of month = Sumx(filter(Date, [Month year] = earlier([Month year])),[Work Day])

This Day = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Work Date cont Rank]=max('Date'[Work Date cont Rank])))
Last work day = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Work Date cont Rank]=max('Date'[Work Date cont Rank])-1))

2ns last work day =CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Work Date cont Rank]=max('Date'[Work Date cont Rank])-2))

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

Re: Count Rows based on Previous WeekDay

Sudhakar510 — Mon, 24 Oct 2022 06:26:38 GMT

You are star. Thank you very much Amit.