Forum Discussion

Post Partisan

5 years ago

Solved

default filter date, Switch statement

Hello i have the follwoing code to identify today.

Is Today = Switch(True(),

[Date] = Today(), "Today",

Format([Date], "DD/MM/YYYY"))

is there a method to identify last working day. i do have a "is holday" and "is weekday" which return 1,0.

e.g

IsHoliday =

IF ( 'DIM - Date Table'[Date] IN DISTINCT ('DIM - Holiday'[Holidays]), 1, 0 )

IsWorkingDay = IF (NOT('DIM - Date Table'[Day]= "Saturday" || ('DIM - Date Table'[Day]= "Sunday")),1,0)

Thanks all

amitchandak
5 years ago
NewbieJono , Try two columns like

WorkingDay = if(WEEKDAY([Date],2) >=6,0,1)

Is Today =
var _max = maxx(filter('Date', 'Date'[Date] <=today() && [WorkingDay] =1),'Date'[Date])
return
Switch(True(),
[Date] = _max, "Today",
Format([Date], "DD/MM/YYYY"))

3 Replies

amitchandak
Super User
5 years ago
NewbieJono , Try two columns like

WorkingDay = if(WEEKDAY([Date],2) >=6,0,1)

Is Today =
var _max = maxx(filter('Date', 'Date'[Date] <=today() && [WorkingDay] =1),'Date'[Date])
return
Switch(True(),
[Date] = _max, "Today",
Format([Date], "DD/MM/YYYY"))
- NewbieJono
  Post Partisan
  5 years ago
  is there anuthing i can do to show last working day,e.g not weekends
v-deddai1-msft
Community Support
5 years ago
Hi NewbieJono ,

Please try to change your isworkingday column to :

IsWorkingDay = IF ( WEEKDAY('DIM - Date Table'[Day],2)<6,1,0)

Then use the following column to determine the last working day:

lastworkingday = IF('DIM - Date Table'[Day] = CALCULATE(MAX('DIM - Date Table'[Day]),FILTER('DIM - Date Table','DIM - Date Table'[IsHoliday] = 1&&'DIM - Date Table'[IsWorkingDay] = 1&&'DIM - Date Table'[Day]<=Tody())),1,0)

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

Related content