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.
Hi Everyone,
I have few date fields in Dimension table, invoice date, due date and Paid date.
Based on the dates I need to calculate the Days Outstanding as a measure in the fact.
any help is really appreciated.
Thanks in advance.
invoice no | invoice date | due date | Paiddate | Days Outstanding | |
20 | 01-06-2019 | 01-07-2019 | 15-08-2019 | 45 | paid date - due date |
22 | 01-07-2019 | 01-08-2019 | 01-10-2019 | 61 | paid date - due date |
25 | 01-10-2019 | 03-10-2019 | 19 | today - due date | |
28 | 02-10-2019 | 22-10-2019 | 15-10-2019 | -7 | paid date - due date |
28 | 02-10-2019 | 03-11-2019 | due date after today, then 0 |
Here is my dax, but it doesnt seem to work.
SWITCH (
TRUE (),
ISBLANK('Sales Transaction Info'[Date of Paid]) &&
'Sales Transaction Info'[Due Date] < TODAY(), DATEDIFF ( 'Sales Transaction Info'[Due Date], TODAY(), DAY ),
DATEDIFF ( 'Sales Transaction Info'[Date of Paid], 'Sales Transaction Info'[Due Date], DAY ),
0
)
Solved! Go to Solution.
Hi medaxing,
The sum of grand total =45 is automatical aggregation of Powerbi, it will refer to measure function(min) to use min value of each field to calculate. In addition, which part of result did you get 0, could you please inform me in details(by screenshots)?
By the way, you also could use calculated column to achieve this goal
Column = if(t2[due date]>TODAY() ,0 ,if(t2[Paiddate]=BLANK(), TODAY()-t2[due date],t2[Paiddate]-t2[due date]))
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi medaxing,
You could try below measure
Measure = IF ( MIN ( t2[due date] ) > TODAY (), 0, IF ( MIN ( t2[Paiddate] ) = BLANK (), TODAY () - MIN ( t2[due date] ), MIN ( t2[Paiddate] ) - MIN ( t2[due date] ) ) )
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for the quick response.
Somehow, I get zero as the result. also wondering how do get the sum as 45?
Hi medaxing,
The sum of grand total =45 is automatical aggregation of Powerbi, it will refer to measure function(min) to use min value of each field to calculate. In addition, which part of result did you get 0, could you please inform me in details(by screenshots)?
By the way, you also could use calculated column to achieve this goal
Column = if(t2[due date]>TODAY() ,0 ,if(t2[Paiddate]=BLANK(), TODAY()-t2[due date],t2[Paiddate]-t2[due date]))
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |