Help with dax calculation - outstanding complaints

DAX DAX Calculations
1

Hi,

Please I need to calculate the lenght of days of an outstanding complaints.

Below I have number of outstanding complaints and a start date. The end date is 1/12/2019

So I want a dax code which can calculate how many days has the complaints been open.For example on the 10/07/2019 we have 2 outstanding complaints open, to get the lenght of days will be:1/12/2019 - 10/07/2019 will be 144 days open. How do I calculate it as a measure without typying the dates manually.

Thanks,
Chukliz

Outstanding Start Date Length of days
1 01/04/2019 00:00
1 29/04/2019 00:00
1 08/05/2019 00:00
1 09/05/2019 00:00
1 10/05/2019 00:00
1 28/05/2019 00:00
1 10/06/2019 00:00
1 14/06/2019 00:00
1 18/06/2019 00:00
1 28/06/2019 00:00
1 02/07/2019 00:00
1 09/07/2019 00:00
2 10/07/2019 00:00
1 11/07/2019 00:00
1 16/07/2019 00:00
1 22/07/2019 00:00
1 23/07/2019 00:00
1 24/07/2019 00:00
1 26/07/2019 00:00
1 05/08/2019 00:00
1 07/08/2019 00:00
2 09/08/2019 00:00
2 12/08/2019 00:00
2 13/08/2019 00:00
1 14/08/2019 00:00
2 15/08/2019 00:00
1 22/08/2019 00:00
1 23/08/2019 00:00
3 28/08/2019 00:00
1 29/08/2019 00:00
1 30/08/2019 00:00
1 02/09/2019 00:00
1 03/09/2019 00:00
3 04/09/2019 00:00
1 05/09/2019 00:00
4 09/09/2019 00:00
1 10/09/2019 00:00
2 11/09/2019 00:00
2 13/09/2019 00:00
1 17/09/2019 00:00
3 19/09/2019 00:00
3 23/09/2019 00:00
2 24/09/2019 00:00
3 25/09/2019 00:00
2 26/09/2019 00:00
1 27/09/2019 00:00
1 30/09/2019 00:00
2 01/10/2019 00:00
4 03/10/2019 00:00
2 04/10/2019 00:00
2 07/10/2019 00:00
3 08/10/2019 00:00
5 09/10/2019 00:00
1 10/10/2019 00:00
2 11/10/2019 00:00
6 14/10/2019 00:00
6 15/10/2019 00:00
2 16/10/2019 00:00
4 17/10/2019 00:00
4 18/10/2019 00:00
3 21/10/2019 00:00
6 22/10/2019 00:00
3 23/10/2019 00:00
3 24/10/2019 00:00
4 25/10/2019 00:00
10 28/10/2019 00:00
7 29/10/2019 00:00
7 30/10/2019 00:00
3 31/10/2019 00:00
2 01/11/2019 00:00
2 04/11/2019 00:00
2 05/11/2019 00:00
1 06/11/2019 00:00
1 08/11/2019 00:00
3 11/11/2019 00:00
1 12/11/2019 00:00
3 13/11/2019 00:00
1 14/11/2019 00:00
3 15/11/2019 00:00
4 18/11/2019 00:00
4 19/11/2019 00:00
1 20/11/2019 00:00
2 21/11/2019 00:00
1 25/11/2019 00:00
3 26/11/2019 00:00
4 27/11/2019 00:00
3 28/11/2019 00:00
2 29/11/2019 00:00
201

2

Hi, Chukliz! Have you consider to use DATEDIFF function?

3

Sorry I forgot to mention that the dates in my table is upto 12/31/2019 but I only want the End date for this calculation to be 01/12/2019

4

Have you tried to put DATE(2019,12,01) as the End Day?
= DATEDIFF (DATE (2019,4,1), DATE (2019,12,1), DAY)

5

Hi you’ve got serveral options here, first like @Ivanka suggested:

Length DATEDIFF =
VAR StartDate = SELECTEDVALUE( tOutstandingComplaints[StartDate] )
VAR EndDate = DATE(2019, 12, 1)
RETURN

DATEDIFF( StartDate, EndDate, DAY )

.
But you could also use COUNTROWS in your Dates table:

Length COUNTROWS = 
VAR StartDate = SELECTEDVALUE( tOutstandingComplaints[StartDate] )
VAR EndDate = DATE(2019, 12, 1)
RETURN

CALCULATE(
    COUNTROWS( Dates ),
    FILTER( ALL(Dates),
    Dates[Date] > StartDate &&
    Dates[Date] <= EndDate
    )
)

.
The bonus here is that you could also exclude weekends:

Length COUNTROWS No Weekends = 
VAR StartDate = SELECTEDVALUE( tOutstandingComplaints[StartDate] )
VAR EndDate = DATE(2019, 12, 1)
RETURN

CALCULATE(
    COUNTROWS( Dates ),
    FILTER( ALL(Dates),
    Dates[Date] > StartDate &&
    Dates[Date] <= EndDate &&
    Dates[IsWorkingDay] = TRUE()
    )
)

Here’s a sample file: eDNA Forum - Outstanding Complaints.pbix (99.5 KB)
I hope this was helpful

1 Like
6

Hi All,

Thank you for your help. I was able to resolve it with your solution.

Chukliz…