Dec 07 2021 10:14 PM
Hello, I need help with DAX to analyse the data in this dataset. I want to plot on the same graph, by month (e.g. from 10/2020 to date) the following information. Can you please help?
1. Monthly number of request submitted (= count of submission date)
2. Monthly number of request closed (= count of closed date)
3. Monthly overdue request = total of overdue request as at end of each month (request is overdue if it is committed to respond prior to end of the month, and has not been respond as at end of month)
Request No | Submission Date | Commit Response Date | Respond Date | Close Date | Status |
Dec 08 2021 08:25 AM
Let assume we have such table added to data model
We need to add additionally calendar (Date) table. To simplify I generated it in Power Pivot. Next let create relationships of our table with it on each type of date we use into analysis
In Date use Sort by column option to sort MMM-YYYY in proper way.
Create calculate column Overdue as
= Table1[Respond Date] > EOMONTH( Table1[Commit Response Date], 0)
Our measures could be
number of request submitted:=COUNTROWS( Table1 )
number of request closed :=
CALCULATE (
COUNTROWS ( Table1 ),
NOT (
ISBLANK ( Table1[Close Date] )
),
USERELATIONSHIP ( 'Date'[Date], Table1[Close Date] )
)
overdue request :=
CALCULATE (
COUNTROWS ( Table1 ),
Table1[overdue],
USERELATIONSHIP ( 'Date'[Date], Table1[Commit Response Date] )
)
Result is
Dec 12 2021 09:28 PM
@Sergei Baklan Thank you, great help. However the overdue request calculation gives me only the overdue request for the month of interest (e.g. 4 overdue in Feb). If there is 1 committed response in Jan, and still overdue, it doesn't give the correct current 5 overdue. Are you able to check? Thank you.
Dec 13 2021 03:01 AM
That's better to have sample file, in above I didn't find 4 overdue for Feb. Anyway, that could be
overdue request:=VAR minDate =
MIN ( 'Date'[Date] )
VAR maxDate =
MAX ( 'Date'[Date] )
VAR withDate =
CALCULATE (
COUNTROWS ( Table1 ),
ALL ( Table1 ),
Table1[Commit Response Date] <= maxDate,
Table1[Respond Date] > maxDate,
USERELATIONSHIP ( 'Date'[Date], Table1[Commit Response Date] )
)
VAR noResponse =
CALCULATE (
COUNTROWS ( Table1 ),
ALL ( Table1 ),
Table1[Commit Response Date] < minDate,
ISBLANK ( Table1[Respond Date] ),
USERELATIONSHIP ( 'Date'[Date], Table1[Commit Response Date] )
)
RETURN
withDate + noResponse