Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi guys,
I need to calculate the number of days a case was delayed. I have 2 date columns in my main table 'Resolution Date' and 'Resolution Due By'.
I have created a column in the date table which mentions if it is a working day or no.
I need to create a column that calculated the delay (working days only) with the following logic:
if(resolution date = blank(), if(today()>resolution due by,(today()-resolution due by),0),(resolution date-resolution due date))
Solved! Go to Solution.
Just reread your question, and tweaked the DAX a little so that it substitutes any blank in Resolution Date with the current date, and then works out the delay. One important thing to point out is that you will need to have your date table up to the current date or the DAX will return an error of "An invalid numeric representation of a date value was encountered"
=
VAR ResolutionDate = IF(ISBLANK(Resolution[Resolution Date]),TODAY(),Resolution[Resolution Date])
RETURN
IF (
ResolutionDate > Resolution[Resolution Due By],
CALCULATE (
COUNTROWS ( 'Date' ),
DATESBETWEEN (
'Date'[Date],
Resolution[Resolution Due By],
ResolutionDate - 1
),
'Date'[IsWorkingDay] = 1,
ALL ( Resolution )
),
- CALCULATE (
COUNTROWS ( 'Date' ),
DATESBETWEEN (
'Date'[Date],
ResolutionDate ,
Resolution[Resolution Due By] - 1
),
'Date'[IsWorkingDay] = 1,
ALL ( Resolution )
)
)
Just reread your question, and tweaked the DAX a little so that it substitutes any blank in Resolution Date with the current date, and then works out the delay. One important thing to point out is that you will need to have your date table up to the current date or the DAX will return an error of "An invalid numeric representation of a date value was encountered"
=
VAR ResolutionDate = IF(ISBLANK(Resolution[Resolution Date]),TODAY(),Resolution[Resolution Date])
RETURN
IF (
ResolutionDate > Resolution[Resolution Due By],
CALCULATE (
COUNTROWS ( 'Date' ),
DATESBETWEEN (
'Date'[Date],
Resolution[Resolution Due By],
ResolutionDate - 1
),
'Date'[IsWorkingDay] = 1,
ALL ( Resolution )
),
- CALCULATE (
COUNTROWS ( 'Date' ),
DATESBETWEEN (
'Date'[Date],
ResolutionDate ,
Resolution[Resolution Due By] - 1
),
'Date'[IsWorkingDay] = 1,
ALL ( Resolution )
)
)
Hi, @yashponde
Please correct me if I wrongly understood your question.
I assume today() = selected date from a slicer.
Please check the below picture if that is what you are looking for.
The sample pbix file's link is down below, and the measure is in the file as well.
https://www.dropbox.com/s/6l7bxkn50mzd6f7/yashponde.pbix?dl=0
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi @yashponde
You can refer to this link from SQLBI on counting the number of working days.
In your case, however there are a few additional logic to take care of - cases where the Resolution Date could be blank, and also cases where Resolution Date is greater than Resolution Due By date, and vice versa.
A simple way is just to take care of these with IF statements. A calculated column would be like below. Also note that your date table needs to cover the full range of possible dates in Table1 (which I have named Resolution table in my code below)
=IF (
NOT ISBLANK ( Resolution[Resolution Date] ),
IF (
Resolution[Resolution Date] > Resolution[Resolution Due By],
CALCULATE (
COUNTROWS ( 'Date' ),
DATESBETWEEN (
'Date'[Date],
Resolution[Resolution Due By],
Resolution[Resolution Date] - 1
),
'Date'[IsWorkingDay] = 1,
ALL ( Resolution )
),
- CALCULATE (
COUNTROWS ( 'Date' ),
DATESBETWEEN (
'Date'[Date],
Resolution[Resolution Date],
Resolution[Resolution Due By] - 1
),
'Date'[IsWorkingDay] = 1,
ALL ( Resolution )
)
)
)
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
21 | |
15 | |
14 | |
11 | |
9 |
User | Count |
---|---|
28 | |
23 | |
12 | |
11 | |
10 |