cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Garol Regular Visitor
Regular Visitor

Business Date Difference Between Open and Closed Using Date Table

Hello

 

I've looked through a number of threads on business datediff but a lot of them are focused on only counting non-weekend days. I haven't been able to find a thread on summing the number of business days using a date table where the business day is flag as 1 or 0. 

 

 

I have two tables in my datasource. Table1 is my main data and I'm needing to return the sum of business days from my secondary table, the Dim_Date[Business Day Flag] = 1 between Table1[OpenDate] and Table1[ClosedDate].

 

Table2 is my date table which does contain a 1 or a 0 for business day flag.  This flag is purpose built and has all public holidays and non-working days built into it's logic. 

 

Table2 is joined to the datasource and connected to Table1.[OpenDate].

 

I cannot attach a file due to sensativity. Can anyone help?

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-jiascu-msft Super Contributor
Super Contributor

Re: Business Date Difference Between Open and Closed Using Date Table

Hi @Garol,

 

Please check out the demo in the attachment. One NOTE: the Cross Filter Direction should be Single.

BusinessDays =
CALCULATE (
    SUM ( Dim_Date[Business Day Flag] ),
    FILTER (
        'Dim_Date',
        'Dim_Date'[Date] >= [OpenDate]
            && 'Dim_Date'[Date] <= Table1[ClosedDate]
    )
)

Business_Date_Difference_Between_Open_and_Closed_Using_Date_Table

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 REPLIES 5
v-jiascu-msft Super Contributor
Super Contributor

Re: Business Date Difference Between Open and Closed Using Date Table

Hi @Garol,

 

Please check out the demo in the attachment. One NOTE: the Cross Filter Direction should be Single.

BusinessDays =
CALCULATE (
    SUM ( Dim_Date[Business Day Flag] ),
    FILTER (
        'Dim_Date',
        'Dim_Date'[Date] >= [OpenDate]
            && 'Dim_Date'[Date] <= Table1[ClosedDate]
    )
)

Business_Date_Difference_Between_Open_and_Closed_Using_Date_Table

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Garol Regular Visitor
Regular Visitor

Re: Business Date Difference Between Open and Closed Using Date Table

Thanks for this. I think i'm close!

 

I've checked the relationship and that matches the solutions.

 

I'm getting the following error. Do i have to mix/max something here?

I don't know why i can't just copy and paste your code, given it works in your example!

Could this be a version issue with Power BI Desktop?error1.PNG

error1.PNG

Garol Regular Visitor
Regular Visitor

Re: Business Date Difference Between Open and Closed Using Date Table

Hi there again.

 

I went ahead and adding the min and max functions. Which will work if i can add context for a Table1[RecordID], 
How would you suggest i add in row level context for a RecordID to this calcuation?

 

BusinessDays =
CALCULATE (
    SUM(Dim_Date[BusinessDayFlag]),
    FILTER (
        'Dim_Date',
        'Dim_Date'[Date] >= MIN(Table1[OpenDate])
            && 'Dim_Date'[Date] <= MAX(Table1[CloseDate])
    )
)

Garol Regular Visitor
Regular Visitor

Re: Business Date Difference Between Open and Closed Using Date Table

Massive amatuer hour. The issue was i was using a measure...not a column. Solution is great.

Highlighted
v-jiascu-msft Super Contributor
Super Contributor

Re: Business Date Difference Between Open and Closed Using Date Table

Hi @Garol,

 

I' m glad you solved it. The calculated column is better than a measure in this scenario.

 

 

Best Regards,
Dale

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

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 316 members 3,137 guests
Please welcome our newest community members: