Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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))
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).
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 🙂
Thank you, but it does not solve second issue with this measure.
Anyone?
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.
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
51 | |
46 | |
39 | |
19 | |
19 |