topic Re: DAX for max overdue (days) payment for any given day in DAX Commands and Tips

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

Hermes — Fri, 27 Sep 2019 09:57:52 GMT

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.

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

Anonymous — Fri, 27 Sep 2019 10:41:22 GMT

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

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

Hermes — Fri, 27 Sep 2019 11:59:01 GMT

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

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

Anonymous — Fri, 27 Sep 2019 12:06:06 GMT

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

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

Hermes — Fri, 27 Sep 2019 12:41:51 GMT

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

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

Hermes — Tue, 01 Oct 2019 06:49:29 GMT

Anyone?

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

Anonymous — Tue, 01 Oct 2019 06:53:46 GMT

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.

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

Hermes — Tue, 01 Oct 2019 13:46:53 GMT

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?