topic Re: Flag controlling for latest weekday in DAX Commands and Tips

Flag controlling for latest weekday

Anonymous — Mon, 31 Jan 2022 10:12:12 GMT

Hi,

I have created a report displaying latest data from the day before. However, due to the weekends, mondays data is not showing the correct values, as the servers are not running in the weekend. Therefore, I need a measure/column that can control for this. Can anyone be of help?

I have some very sensitiv data, why I can't share the file. If its not possible to help without, I will make a example file.

Thanks in advance!

Re: Flag controlling for latest weekday

amitchandak — Mon, 31 Jan 2022 10:45:37 GMT

Anonymous , try like

new columns

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)

In you case you might have to use

Work Date cont Rank = if([Work Day] =0 , blank(), RANKX(ALL('Date'),[Work Date],,ASC,Dense) )

Then measure like

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))
diff =[This Day] - [Last work day]

Traveling Across Workdays - What is next/previous Working day
https://community.powerbi.com/t5/Community-Blog/Travelling-Across-Workdays-Decoding-Date-and-Calendar-4-5-Power/ba-p/1187766

Re: Flag controlling for latest weekday

Anonymous — Mon, 31 Jan 2022 13:29:13 GMT

Hi,

I have a date table that I use for filtering out, so I tried the following:

PreviousWorkingDay_Yesterday SWITCH =
SWITCH(TRUE(),
WEEKDAY ( TODAY ()-1, 2 ) < 5, TODAY () - 1,
WEEKDAY ( TODAY ()-1, 2 ) = 6, TODAY () - 2,
TODAY () - 3
)

PreviousWorkingDay_Yesterday_flag SWITCH = IF(
Dates[PreviousWorkingDay_Yesterday SWITCH]=Dates[Date],TRUE()
,FALSE()
)

Just a bit unsure where to apply it.... Because if I add it to the filter and choose all values with TRUE it doesnt display anything. But I can see it from my table like below:

Re: Flag controlling for latest weekday

Anonymous — Thu, 03 Feb 2022 09:27:23 GMT

How should I apply the measure with variables with below binding date table: