DAX for max overdue (days) payment for any given day

[Hermes]

Hello,

Could anyone please help me with creating measure returning max overdue in days for any given day for transaction unpaid in time? Basicaly; current date - min([due date]), but I have no idea how to filter only transactions unpaid ( payment[amount] - payment sum(transfer[amount]) <> 0 )for given day.

Here is simplified tables I work with, with balance already calculated and desired output to be calculated (in orange). Chart does not matter, but it shows what I want to achieve.

   

my simplified model attached - https://we.tl/t-S4tlqJTDGj

Any help would be appreciated. 

[Anonymous]

Hi,

Can you try using the below Formula.

Measure:= CALCULATE(DATE(TODAY())-MIN(Due[Due Date]),
CALCULATETABLE(ADDCOLUMNS(SUMMARIZECOLUMNS(PAYMENT[Receive Date],"Payment Amount",
SUM(PAYMENT[Amount]),"Due Sum",CALCULATE(SUM(Due[Amount]), 
TREATAS(VALUES(PAYMENT[Receive Date]),Due[Due Date]))),"Difference", 
[Payment Amount]-[Due Sum]),[Difference]<>0))

[Hermes]

Hello Mvignesh53

This measure does not work for me:

DATE(TODAY())

This is not accepted as valid syntax

Moreover, trying to refrence [Difference] in filter [Difference] <> 0 gives me error "column does not exist".

It might be because I work in excel power pivot, where DAX engine is somehow limited (i.e. TREATAS is not recognized as formula; yet still works, which is buffling).

[Anonymous]

Hi,

Sorry about that.Can you replace it with below.

Date:= DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))

Best Regards,
Vignesh M

If what I suggested worked for you feel free to Drop a "Kudos" and Consider to "Accept as Solution" if I solved your Issue 🙂

[Hermes]

Thank you, but it does not solve second issue with this measure. 

[Hermes]

Anyone?

[Anonymous]

Hi,

Can you share this pbix file.

Best Regards,
Vignesh M

If what I suggested worked for you feel free to Drop a "Kudos" and Consider to "Accept as Solution" if I solved your Issue.

[Hermes]

I've shered excel with data model already - https://we.tl/t-S4tlqJTDGj - do You need it converted to pbix, or is it fine like this?