- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Datediff with excluding weekend with column condition / filter
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.
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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")
)
)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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")
)
)
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - January 2025
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
Subject | Author | Posted | |
---|---|---|---|
10-31-2023 01:00 AM | |||
12-13-2023 06:38 AM | |||
Anonymous
| 11-08-2023 03:28 AM | ||
08-09-2024 12:16 AM | |||
09-20-2023 06:28 AM |
User | Count |
---|---|
122 | |
80 | |
47 | |
45 | |
35 |
User | Count |
---|---|
178 | |
89 | |
69 | |
47 | |
47 |