Build dax query to get predicted overdues

[miracle2023]

Hi there, 

I wonder does anyone have any idea how to do it via dax query (please see the attached screenshot)?

I've already got a number of overdue cases until now, due cases in current month and also due cases for next months. Availible FTE is a static variable.

I want to calculate potential overdues that will happen in future: (previous month overdue + current month due) - availible fte. 

For instance, to calculate September overdues - (I take real number of overdues + due in September) - Availible FTE

To calculate October - (September Overdues + due in October) - Availible FTE.

Any help would be valuable,

Thank you.

[miracle2023]

Still struggling with it. Any help would be appreciated.

[miracle2023]

any suggestions?

Thanks

[miracle2023]

Hi,

Thank you very much for you suggestion.

However, it is not something I would expect in my output.

I have three measures:

Due_cases = calculate(sum(table[cases]),status="Due")

Due_cases_in_current_month = calculate(sum(table[cases]),status="Due", FILTER(table,EOMONTH(table[date],0)=EOMONTH(TODAY(),0)))

Overdue_cases = calculate(sum(table[cases]),status="Overdue")

Matrix should look:

Date	August 2023	September 2023	October 2023	November 2023
Overdue	25 (Overdue_cases)	(37+25)-2.5=59.5	(59.5 + 23) - 2.5 = 80	(80+11)-2.5 = 88.5
Due	40 (due cases in current month)	37 (due cases)	23 (due cases)	11 (due cases)
FTE Availible	2.5	2.5	2.5	2.5

I would like to get these overdues for upcoming 15 months.

I would appreciate any help,

Thank you

[JoeBarry]

Hi @miracle2023 

Can you share a screenshot of the data in the table if it isn't sensitive?

I would like to see how it's structured and how you get to overdue. 

Thanks

[miracle2023]

Thank you for your reply.

Please see the attached screenshot. 

[JoeBarry]

Hi @miracle2023 

I will presume that in your dataset you have data indicating that something is overdue?

Create a Measure called OverDue 

OverDue = CALCULATE(DISTINCTCOUNT(Table[CaseID]), KEEPFILTERS(Table[CaseOverDue]= "YES")

 Then you need a measure to calculate last months values

LastMonth = CALCULATE([OverDue], DATEADD(Date[Date], -1, Month))

Then create the measure you need for your calculation

Predicted Overdues = ([LastMonth] + [OverDue]) - 2.5 ///or use MAX(Table[AvailableFTE]) ///

Thanks

Joe

If this post helps, then please Accept it as the solution