Calculate Overdue Between Dates

[Anonymous]

Hello,

I'm a new DAX user, can you please help. I'm trying to plot a dashboard that analyses data provide monthly cut off report: Total submit request in month, total close request in month and overdue request by end of each month. For the below table, reporting by end of Feb, total submit in FEb= 5, Total close in Feb = 2, Total overdue = 2 as at 28/2.  I don't know how to calculate the Total Overdue, can you please help?

Request No	Submit Date	Committed Response Date	Response Date	Open/Close
1	1/2/21	14/2/21	14/2/21	Close
2	2/2/21	15/2/21	17/2/21	Close
3	2/2/21	15/2/21	3/3/21	Close
4	4/2/21	16/2/21		Open
5	22/2/21	15/3/21		Open

[v-yalanwu-msft]

Hi, @Anonymous ;

You could create a measure as follows:

total submit = CALCULATE(COUNT('Table'[Request No]),FILTER(ALL('Table'),EOMONTH([Submit Date],0)=EOMONTH(MAX('Table'[Submit Date]),0)))

Total close = 
CALCULATE (
    COUNT ( 'Table'[Request No] ),
    FILTER (
        ALL ( 'Table' ),
        EOMONTH ( [Submit Date], 0 ) = EOMONTH ( MAX ( 'Table'[Submit Date] ), 0 )
            && EOMONTH ( [Response Date], 0 ) = EOMONTH ( MAX ( 'Table'[Submit Date] ), 0 )
            && [Open/Close] = "Close"
    )
)

Total overdue = CALCULATE(COUNT('Table'[Request No]),FILTER(ALL('Table'),EOMONTH([Submit Date],0)<=EOMONTH(MAX('Table'[Submit Date]),0)&&[Open/Close]="Open"))

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@amitchandak Thank you. However the closed measure gives me the total closed, not the closed request in the reporting period. For instance my table in the question, if reporting at 28/2/21, only 2 (request 1 & 2) are closed. Request 3 was not closed until sometime in March. 

To clarify, I have a dataset for requests from October 2020 to date. I want to analyses the data, to see in each month, how many TOTAL REQUEST were raised (by count of request submitted in month), Total Closed (by count of those closed in the Month, regardless of on time or overdue), and Total overdue request by Month (count of request committed to response in the month, but still outstanding + plus any request in previous month that is still outstanding).

[amitchandak]

@Anonymous , if you are looking open based on close and open date ?

then my HR blog can help https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

Current employee code should work for you

[amitchandak · Accepted solution]

@Anonymous , it should be 3/2 or 28 by two ,

To get 28 means day of max selected date

Try measures

Days = Day(maxx(allselected('Date'), 'Date'[Date]))

Total  = Distinctcount(Table[Request No]) // Or //count(Table[Request No])

or

Total  =calculated( count(Table[Request No]), allselected())

closed =calculated( count(Table[Request No]), Filter(Table, Table[Open/Close] = "Close"))