Forum Discussion

miracle2023's avatar
miracle2023
Icon for Helper I rankHelper I
3 years ago

Dynamic sum function to get assumed overdue cases in future

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

 

12 Replies

  • some_bih's avatar
    some_bih
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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's avatar
      miracle2023
      Icon for Helper I rankHelper I
      3 years ago

      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. 

       

       

       

    • miracle2023's avatar
      miracle2023
      Icon for Helper I rankHelper I
      3 years ago

      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's avatar
        some_bih
        Icon for Community Champion rankCommunity Champion
        3 years ago

        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's avatar
        some_bih
        Icon for Community Champion rankCommunity Champion
        3 years ago

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