DAX SSAS : Calculate days measure from another table

[medaxing]

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
)

[dax]

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.

[medaxing]

Thanks for the quick response.

Somehow, I get zero as the result. also wondering how do get the sum as 45?

[dax · Accepted 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.