Forum Discussion
Helper IIINewbiew - Time intelligence - holliday table
Hi
I'm brand new with PBI and im already in way over my head.
I have to do some leadtime calculation for our parcels. In my dataset I have a start date ( CMR_Closed ) and several different end dates. Ex.: LM_FirstScan.
I want to calc work days between these to. That part is easy enough. But I want to subsctract hollidays in the receiving country. And I have made a table with these dates as well.
This is my fact table:
This is my holliday table:
I've tried to share the pbix file here:
https://drive.google.com/drive/folders/1FIWVBvxyQ-YpzQj6G4Cq9w1OkuDjrVnP?usp=sharing (I cant add pbix files in the thread here?)
Thanks in advance,
DennisSchlein , Create a measure like
Work Day = COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR([LM_FirstScan],[CMR_Closed]),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1))
- countx(filter(Holiday,Holiday[Country] = table[Country] && table[Date] >= [LM_FirstScan] && table[Date] <= [CMR_Closed]),Holiday[Country])
Work Day = COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR([LM_FirstScan],[CMR_Closed]),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1))
- countx(filter(Holiday,Holiday[Country] = table[Country] && table[Date] >= [LM_FirstScan] && table[Date] <= [CMR_Closed]),Holiday[Country])or
Work Day = datediff(LM_FirstScan],[CMR_Closed], day) +1
- countx(filter(Holiday,Holiday[Country] = table[Country] && table[Date] >= [LM_FirstScan] && table[Date] <= [CMR_Closed]),Holiday[Country])Hi DennisSchlein ,
I change your code for DateTable as this:
DateTable = FILTER( ADDCOLUMNS ( CALENDARAUTO(), "Year", YEAR ( [Date] ), "Month", FORMAT ( [Date], "mmmm" ), "Month number", MONTH ( [Date] ), "Quater", FORMAT ( [Date], "\QQ"), "Week", WEEKNUM( [Date], 2), "Day", DAY ( [Date] ) ), WEEKDAY([Date],2)<6 && not([Date] in VALUES(HOLIDAYS[DATE])))Pbix in the end you can refer, if i misunderstood you please let me know.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
- amitchandak
Super User
DennisSchlein , Create a measure like
Work Day = COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR([LM_FirstScan],[CMR_Closed]),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1))
- countx(filter(Holiday,Holiday[Country] = table[Country] && table[Date] >= [LM_FirstScan] && table[Date] <= [CMR_Closed]),Holiday[Country])
Work Day = COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR([LM_FirstScan],[CMR_Closed]),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1))
- countx(filter(Holiday,Holiday[Country] = table[Country] && table[Date] >= [LM_FirstScan] && table[Date] <= [CMR_Closed]),Holiday[Country])or
Work Day = datediff(LM_FirstScan],[CMR_Closed], day) +1
- countx(filter(Holiday,Holiday[Country] = table[Country] && table[Date] >= [LM_FirstScan] && table[Date] <= [CMR_Closed]),Holiday[Country]) - v-chenwuz-msft
Community Support
Hi DennisSchlein ,
I change your code for DateTable as this:
DateTable = FILTER( ADDCOLUMNS ( CALENDARAUTO(), "Year", YEAR ( [Date] ), "Month", FORMAT ( [Date], "mmmm" ), "Month number", MONTH ( [Date] ), "Quater", FORMAT ( [Date], "\QQ"), "Week", WEEKNUM( [Date], 2), "Day", DAY ( [Date] ) ), WEEKDAY([Date],2)<6 && not([Date] in VALUES(HOLIDAYS[DATE])))Pbix in the end you can refer, if i misunderstood you please let me know.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.