# Help with DAX

Occasional Contributor

# Help with DAX

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

3 Replies

# Re: Help with DAX

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

# Re: Help with DAX

@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.

# Re: Help with DAX

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
``````