Dynamic sum function to get assumed overdue cases in future

[miracle2023]

Hi,

I'm trying to build dax query which will calculate overdue cases for future.

I have three measures and one calculated column for "Overdue":

Measures:
Current_due = CALCULATE(COUNTROWS(customers),customers[status]="Due",FILTER(customers,EOMONTH(customers[deadline],0)=EOMONTH(TODAY(),0)),customers[Task]="OEDD")

Due_future CALCULATE(countrows(customers]),customersd[status]="Due",customers[Deadline]>TODAY(),customers[Task]="OEDD")

Overdue = CALCULATE(COUNTROWS(customers),USERELATIONSHIP(dimDate[Date],customers[C_C]),customers[C_C]<>BLANK(),customers[Task]="OEDD"

Calculated column: 
C_C = IF(customers[status]="Overdue",TODAY(),BLANK())

Sample data and chart: 

 

I would like to build Area Chart in PowerBI by the following methodology:

We have Overdues by today and Due in future. Hence, to calculate Overdues that will happen in September - we sum (Overdues + Due in September) - Static value 

Overdues in October = (Overdues in September + Due in October) - Static Value 

Overdues in November = (Overdues in October + Due in November) - Static Value

Ideally, I'd like to get these predictions for upcoming 16 months. 

I would appreciate any help,

Thank you

[some_bih]

Hi @miracle2023 I would say, the best would be to share example file with expected output.

[miracle2023]

Thank you for your reply. Here is the link :

Link 

[some_bih]

Hi @miracle2023 

In your file there are 2 duplicates for Customer ID, this could affect reconciliation results.

Should we remove some rows, some leave? Which to remove?

In pbi there is no "Excel" terms as shown below:

Delivery will be some fix amount?

What about Falling due? It should be what?

On picture okt 2023 amount is negative - this is pure example? Calculation should include MIN (something..., 0), so 0 (zero) is minimum value or ...?

[miracle2023]

Hi, 

Thank you for your reply again.

Yes delivery is static and will not be changed.

Failing Due is Due based on deadline date. 

I should've clarified that excel is basically my real data. I have 1225 overall cases for one team (all due + all overdues).

Negative should stay the same - it means that resposnible team do very well and they have additional resources that can be re-allocated.

[some_bih]

Hi @miracle2023 ok for negative.

Failing Due is Due based on deadline date. - Explain more / what should be calculation logic.

If it is easier to you in file (new one) update data as is your input and expected output just to be more efficient with questions.

[miracle2023]

If I have 2 cases with deadline in October 2023, then I'll have 2 Falling Due in October. It is basically what my measures calculate (due in future and current due).

Sorry I think I had to rename it for simplicity.

[some_bih]

Hi @miracle2023 how to identify this measure case?

[miracle2023]

Measures are given in the provided pbi file and in my post message:

Measures:
Current_due = CALCULATE(COUNTROWS(customers),customers[status]="Due",FILTER(customers,EOMONTH(customers[deadline],0)=EOMONTH(TODAY(),0)),customers[Task]="OEDD")

Due_future CALCULATE(countrows(customers]),customersd[status]="Due",customers[Deadline]>TODAY(),customers[Task]="OEDD")

Overdue = CALCULATE(COUNTROWS(customers),USERELATIONSHIP(dimDate[Date],customers[C_C]),customers[C_C]<>BLANK(),customers[Task]="OEDD"

Calculated column: 
C_C = IF(customers[status]="Overdue",TODAY(),BLANK())

[miracle2023]

I provide screenshot because no any sharefile service is availible for me from my work computer.

I'm trying to replicate this Excel in PowerBI. 

 

[some_bih]

Hi @miracle2023 pbi file is need due to context transition and overall model. Share when it is available, with example for expected outpu.

[some_bih]

Hi @miracle2023 each month "next 16 months" will be changed like next 15/17 month or similar...? static value is always static, I min fix amount not like fix amount of september another for october...? Your model have many columns as you use 

USERELATIONSHIP(dimDate[Date]

As you have Date table for dynamic solution, please check formula for running total on link as guide / idea how to implement something which overlap year end.

https://www.sqlbi.com/articles/computing-running-totals-in-dax/ 

kudos appreciated

[miracle2023]

Hi, 

Thank you for your reply.

I want to get predictions for next 16 months. So if current month is September, I'd expect them until January 2025. Static value is constant for all months and should not be changed. 

I doubt that DATESYTD resolves my issue because the main problem is predicted overdues - I struggle to get them for future which change every month.