Dax Calculation Between Dates

[CoordAnalytics]

Hi all, 

I'm still a beginner with the DAX measures and I'm trying to figure out how to do something. I've got a dummy file that's similar to what I'm working with: https://drive.google.com/file/d/1NzAmW3rra0ZzImLWhgSbMyu88RESM7fi/view?usp=sharing

Basically, I have two order tables one with a date the order was placed and one with a date the order was filled. I need to count how many orders took more than 3 business days to fill. I also have a table connected to both order tables with the dates and it has a column with a 1 for business days and a 0 for non-business days. So my thought is to count the business days between the two dates but I'm not sure how to write the dax measure for it. I was hoping for some assistance. I will say that my actual file is more complicated and I'm concerned that table relationships might cause an issue. 

[AilleryO]

That's coming from the link you gave in your first message... ???

Anyway, you should have a connection between your fact tables (orders) and date table, isn't that the case ?

'Cause it's what I understood from your first message. Am I wrong ?

[CoordAnalytics]

Okay, I don't know how that happened but that wasn't my link. I replaced it with a link to my actual file: https://drive.google.com/file/d/1NzAmW3rra0ZzImLWhgSbMyu88RESM7fi/view?usp=sharing

Sorry about the confusion. There isn't a relationship between the order ID numbers in my original file beacause it introduces ambiguity between other tables. I wish I could share my original file but I can't. Would it help to create a table that's just order ID numbers and try creating a relationship for that table to the other two. Or if I change the sources and have a table that has the order placed date and the order filled date in both how could I do it? I'm completely open to suggestions, this one is just beyond my current capabilities. 

[AilleryO]

Hi,

The Excel source I'm refering to is :

But don't worry, I have a very simple solution for you :

Since you have a date table with 0 and 1 in working days column, you just need to sum this column to get what you want. Only rows with 1 will be "counted" in the sum, since the rows with 0 are not changing your sum.

So first you need to filter in a calculate the period you need (ie start date is your orderplaced_date, and end date your orderfilled_date).

You can use function like DATESBETWEEN,

or filter like >orderplaced_date && <orderfilled_date (&& meaning AND operator) to filter the period needed.

And in this period you SUM the column Working days.

Count of days (wk days) = CALCULATE( SUM( [WorkDays] ) , DATESBETWEEN ( [DateColumn from date table] , orderplaced_date , orderfilled_date )

Hope it helps

[CoordAnalytics]

I don't think you're looking at my file. That excel source isn't mine and I don't use onedrive. Looking at that measure I don't know how that would work since there's nothing that connects the order numbers together, if that makes sense. Like I don't know how it could know to calculate the difference in dates for order number 1.

[AilleryO]

Hi,

I tried to download your dummy test but there is only one column with dates (not 2) and the Excel sources are missing. So not easy to send you back an exemple with calculations.

Nevertheless, here are the steps to follow :

1/ Calcultate delay between your 2 dates, using a filter to count only working days (wk days = 1) in your date table.

2/ Write a measure to count the lines with a delay above 3. In this measure use a filter to count lines above 3 in delay, so something like :

Count of above 3 = CALCULATE( COUNT(Column to count) , delay>3 )

or >=3 depends on what you want.

You can use as well DISTINCTCOUNT if needed...

You also have, for working days, the NETWORKDAYS function, butif you alreday have a column in your date table...

Hope it helps

[CoordAnalytics]

There are no excel sources and each table has a column with dates so I'm not sure which you're referring to. It's just a sort of replica of what I'm working with and all tables are built in PBI. There's a date table with the date, the order_filled table has the column for the date the order was filled and the order_placed table has a column for the date the order was placed, and the business_days table has the date and a column to indicated if it's a business day or not. 

Where I get tripped up is how to write the dax measure to count the dates in the bussiness_days table where business_day = 1 based on the difference of order_filled.date - order_placed.date.