Forum Discussion
Count Billing Days between two Periods
In the attachment I have two datasets (days on placement) and (Calendar). (days on placement) [PlacementId] is unique and has field that calculates the days between the [dateBegin] and [Est. Date End]. What I need to know is out of those days, remove Weekends and Holidays. On the (Calendar) table I know the billable days [IsABillingDay].
How do I Calc or measure count the billing days between the [DateBegin] and [Est. Date End] for each [PlacementId] excluding weekends/holidays using the (Calendar)[IsABillingDay].
I almost don't even know where to start/ get me head wrapped around this request. Thanks in advance. Mark
Hi Fro88er ,
Create a Calculated column with the function DATESBETWEEN can get the desired value.
total_billingDay = CALCULATE(
COUNTROWS(DimCalendar),
DATESBETWEEN(DimCalendar[Date],'Days on Placement'[dateBegin],'Days on Placement'[Est. Date End]),
DimCalendar[isABillingDay]=1,
all(DimCalendar)
)
The results are as follows:
Best Regards,
Caiyun Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
Fro88er , I have two file where I try to address work day problem , See of those can help
refer to the page Working Day Diff in below file
https://www.dropbox.com/s/y47ah38sr157l7t/Order_delivery_date_diff.pbix?dl=0
Hi Fro88er
1. Create the date table with full years and including all the years of interest (note that you do not have 2021in it now and that some end dates in your fact table are in 2021)
2. Create a calculated column on your fact table:
New col = CALCULATE ( COUNT ( DimCalendar[Date] ), FILTER ( ALL ( DimCalendar[Date] ), DimCalendar[Date] >= Table1[dateBegin] && DimCalendar[Date] <= Table1[Est. Date End] ), DimCalendar[IsABillingDay] = 1 )Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hi, Fro88er , you might refer to the attached file for solutions by Power Query and DAX measure.
Power Query solution
DAX measure solution, you need to resort to a unrelated calendar table
Hi Fro88er ,
Create a Calculated column with the function DATESBETWEEN can get the desired value.
total_billingDay = CALCULATE(
COUNTROWS(DimCalendar),
DATESBETWEEN(DimCalendar[Date],'Days on Placement'[dateBegin],'Days on Placement'[Est. Date End]),
DimCalendar[isABillingDay]=1,
all(DimCalendar)
)
The results are as follows:
Best Regards,
Caiyun Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
