Forum Discussion

Dax_Rookie's avatar
Dax_Rookie
Frequent Visitor
8 years ago
Solved

Calculating overdues over time with DAX

Hi,   I'm still new to DAX and having trouble wrapping my head around Date Context.   I am trying to calculate overdue receivables at various points in time, i.e. how many receivables were overdu...
  • v-chuncz-msft's avatar
    v-chuncz-msft
    8 years ago

    Dax_Rookie,

     

    Check the DAX below.

    Measure =
CALCULATE (
    SUM ( Duplicatas[Value] ),
    FILTER (
        ALL ( Duplicatas ),
        Duplicatas[State] = "approved"
            && Duplicatas[Due Date] < MIN ( Dates[Date] )
            && (
                Duplicatas[Paid Date] = BLANK ()
                    || Duplicatas[Paid Date] > MAX ( Dates[Date] )
            )
    )
)
  • Dax_Rookie's avatar
    Dax_Rookie
    8 years ago

    Thanks a lot! The following ended up working:

     

    (R$) Overdue Invoices = 
CALCULATE (
    SUM ( Duplicatas[Value] ),
    FILTER (
        ALL ( Duplicatas ),
        Duplicatas[State] = "approved"
            && Duplicatas[Current Due Date] < MIN( MAX( Dates[Date] ), TODAY() )
            && (
                Duplicatas[Paid Date] = BLANK ()
                    || Duplicatas[Paid Date] > MAX ( Dates[Date] )
            )
    )
)
v-chuncz-msft's avatar
v-chuncz-msft
Icon for Community Support rankCommunity Support
8 years ago

Dax_Rookie,

 

You just need to use the measure below.

Measure =
CALCULATE (
    SUM ( Duplicatas[Value] ),
    USERELATIONSHIP ( Duplicatas[Due Date], Dates[Date] ),
    Duplicatas[State] = "approved",
    Duplicatas[Paid Date] = BLANK ()
)
Dax_Rookie's avatar
Dax_Rookie
Frequent Visitor
8 years ago

Thanks a lot v-chuncz-msft!

 

This formula works in parts:

It shows which invoices were due in each month that still haven't been paid. However, in October, it shows invoices that are still current (e.g. due only on Oct-30) as well.

 

What I am looking for is, at any given Dates[Date], the total value of invoices where the Duplicatas[Due Date] < Dates[Date].

 

Basically like this:

 

Note: I am duplicating the SUMIFS because there are two ways an invoice can be over due: Paid Date is empty (invoice hasn't been paid), or Paid Date is > Due Date (invoice was paid late, i.e. was overdue at some point).

  • v-chuncz-msft's avatar
    v-chuncz-msft
    Icon for Community Support rankCommunity Support
    8 years ago

    Dax_Rookie,

     

    Check the DAX below.

    Measure =
CALCULATE (
    SUM ( Duplicatas[Value] ),
    FILTER (
        ALL ( Duplicatas ),
        Duplicatas[State] = "approved"
            && Duplicatas[Due Date] < MIN ( Dates[Date] )
            && (
                Duplicatas[Paid Date] = BLANK ()
                    || Duplicatas[Paid Date] > MAX ( Dates[Date] )
            )
    )
)
    • Dax_Rookie's avatar
      Dax_Rookie
      Frequent Visitor
      8 years ago

      Thanks a lot! The following ended up working:

       

      (R$) Overdue Invoices = 
CALCULATE (
    SUM ( Duplicatas[Value] ),
    FILTER (
        ALL ( Duplicatas ),
        Duplicatas[State] = "approved"
            && Duplicatas[Current Due Date] < MIN( MAX( Dates[Date] ), TODAY() )
            && (
                Duplicatas[Paid Date] = BLANK ()
                    || Duplicatas[Paid Date] > MAX ( Dates[Date] )
            )
    )
)