Datediff with excluding weekend with column condition / filter

[ljr91-us]

Hi Community,

I have a calculated column DateDiff that display the date difference between two days excluding weekend. Now I need to add a condition to the "End Date".  The condition is  'TableB'[Status] = "Approved",  how could I add this condition to the DAX. I hope just use a single DAX without creating extra physical filtered table in the model.

DateDiff = NETWORKDAYS( RELATED('TableA'[Start_Date]), 'TableB'[End_Date] )    

--basically I want to add a condtion on the red column above

I have tried:

DateDiff = NETWORKDAYS(RELATED('TableA'[Start_Date]), MAXX( filter('TableB', 'TableB'[Status] = "Approved"),  [End_Date] ) )

--There is no syntax error but the result is wrong..

Sorry I don't have a sample data, if some can quickly troubleshoot my code, very appreciated.

[VahidDM]

Hi @ljr91-us 

It would be better if you add mre details re the relations between those tables and some sample data and the expected output, BTW, please try this:

DateDiff =
Var _EB =calculate(MAX('TableB'[End_Date]),  'TableB'[Status] = "Approved")
RETURN
NETWORKDAYS( 'TableA'[Start_Date], _EB ) 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

LinkedIn | Twitter | Blog | YouTube 

[ljr91-us · Accepted solution]

Thanks!

I tried your code, it gives "A circular dependency was detected:" error, I modified it little bit it seems worked.

Apologize I should create a short sample data but just don't have the time today. 

NETWORKDAYS

(RELATED(TableA[Start_Date]),
              calculate(max('TableB'[End_Date]),
                            filter(values('TableB'[createdon]), 'TableB'[Status] = "Approved")
                           )
)