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,

 

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] )
            )
    )
)