Help with DAX

Copper Contributor

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 NoSubmission DateCommit Response DateRespond DateClose DateStatus
      

 

3 Replies

@thangvan 

Let assume we have such table added to data model

image.png

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 

image.png

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

image.png

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

@thangvan 

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