Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi!
I need to create a previous working day column in my calendar table which has the following structure:
WorkingDay column = "1" means that we opened our store and I need for each Date value which was the previous day we opened the store. However I am not able to reach my goal because:
Solved! Go to Solution.
Thanks @amitchandak but your Work Date Cont column gives today date (Monday) for today record as Previous Work Day.
I got the solution. Now PrevWorkDay column gives Friday as Previous Work Day for Saturday, Sunday and Monday records.
PreviousWorkDay =
var a = Hoja1[Date]
RETURN CALCULATE(MAX(Hoja1[Date]); FILTER(ALL(Hoja1); Hoja1[Date] < a && Hoja1[WorkingDay] = 1))
@aramirez2 , You need few columns in 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])
Work day of week = Sumx(filter(Date, [Weekyear] = earlier([Weekyear])),[Work Day])
measures
This Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Work Date cont Rank]=max('Date'[Work Date cont Rank])))
Last work day = CALCULATE(sum('order'[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-Calenda...
The measures like
Hi @amitchandak
Thanks for your answer. However your formulas does not seem to show the desired result:
I have already created Workday column with "1" and "0" values. This data is correct and PrevWorkDay is nearly to work properly. I would like to fill up Blank PrevWorkDay cells with the very last work day.
@aramirez2 , I think, in the blog I shared I have updated nonworking dates with the last working dates
Work Date Cont = if([Work Day]=0,maxx(FILTER('Date',[Date]<EARLIER([Date]) && [Work Day]<> EARLIER([Work Day]) ),[Date]),[Date])
Thanks @amitchandak but your Work Date Cont column gives today date (Monday) for today record as Previous Work Day.
I got the solution. Now PrevWorkDay column gives Friday as Previous Work Day for Saturday, Sunday and Monday records.
PreviousWorkDay =
var a = Hoja1[Date]
RETURN CALCULATE(MAX(Hoja1[Date]); FILTER(ALL(Hoja1); Hoja1[Date] < a && Hoja1[WorkingDay] = 1))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |