Calculate Date Status

[AMBP1973]

Hi,

I was wondering if someone could please assist with the following. 

I currently have the following DAX to calculate no. of documents overdue :

Document Total Overdue = COUNTROWS(FILTER('E&T Document Status Tracker',NOT(ISBLANK('E&T Document Status Tracker'[Cancellation Date]))&&'E&T Document Status Tracker'[Cancellation Date]<TODAY()))

I also have another column, which indicates the actual date a document has been approved:

'E&T Document Status Tracker'[Approval Date] 

I would like to show no. of documents overdue at any stage over a period of time i.e. no. in Jul 2023 vs no. in Jul 24. I am thinking where an approval date (or no approval date) has occured after the 'cancellation date'? Could someone please assist with an appropriate measure to calcultae this, and if possible indictae how I can show the status with a date slicer and variance between a date period?

Any assistance would be greatly appreciated.

Regards,

AM

[bhanu_gautam]

@AMBP1973 , To calculate number of documents overdue you can use measure

First make sure you have a date table

Then
Documents Overdue =
CALCULATE(
COUNTROWS('E&T Document Status Tracker'),
FILTER(
'E&T Document Status Tracker',
NOT(ISBLANK('E&T Document Status Tracker'[Cancellation Date])) &&
'E&T Document Status Tracker'[Cancellation Date] < TODAY() &&
(
ISBLANK('E&T Document Status Tracker'[Approval Date]) ||
'E&T Document Status Tracker'[Approval Date] > 'E&T Document Status Tracker'[Cancellation Date]
)
)
)

To calculate variance you can create another measure

Documents Overdue Current Month =
CALCULATE(
[Documents Overdue],
DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -1, MONTH)
)

Documents Overdue Previous Month =
CALCULATE(
[Documents Overdue],
DATESINPERIOD('Date'[Date], MAX('Date'[Date]) - 1, -1, MONTH)
)

Variance =
[Documents Overdue Current Month] - [Documents Overdue Previous Month]

[AMBP1973]

Hello @bhanu_gautam ,

Thankyou for the measures you have provided, much appreciated. I created a Date Table = CALENDARAUTO() to include in these, however I am not getting any results in any visuals or a table?

However, I am not 

[AMBP1973]

Apologies, seems as though the message didn't come through properly.

Further to the above, I am not getting any results in any visuals or a table? Here is the measures I have created with the above date table:

Documents Overdue Current Month =

CALCULATE(

[Documents Overdue],

DATESINPERIOD('Date Table'[Date].[Date], MAX('Date Table'[Date]), -1, MONTH)

)

Documents Overdue Previous Month =

CALCULATE(

[Documents Overdue],

DATESINPERIOD('Date Table'[Date], MAX('Date Table'[Date]) - 1, -1, YEAR)

)

Variance =

[Documents Overdue Current Month] - [Documents Overdue Previous Month]

Could you please assist further? Many thanks.

[shafiz_p]

Hi, @AMBP1973  As I can understand, that you want to calculate number of overdue document and compare with in various time point. If this is the case, I want to share my point of view.

You need 3 column in your table, document submission date, due date, and approval date. If any document approved after due date will be considered over due. You can use filter function to evaluate this as you have done already. 

Another case is, if any document not approved yet and current date passed the due date will be considered overdue.

Once you evaluate no of overdue document, then you can compare between various time point.

Hope this helps!!

If this solved your problem, please mark it as a solution!!