Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I'm still new to DAX and having trouble wrapping my head around Date Context.
I am trying to calculate overdue receivables at various points in time, i.e. how many receivables were overdue in Jan, Feb, Mar... e.g:
Jan > $100k
Feb > $110k
Mar > $80k
I have a transactions table ("Duplicatas") with the following columns: [State] ("approved" or "rejected"), [Purchase Date], [Due Date], [Paid Date]. There is an active connection between my [Purchase Date] and the Dates table, and inactive connections between the [Due Date] and [Paid Date] columns and the Dates table.
I want to calculate Overdues as Invoices which have [State] = "approved", [Paid Date] = BLANK () (i.e. have not been paid yet / are still oustanding), and [Due Date] < "the date of the period I am comparing to (Jan, Feb, Mar, etc.)". I am not clear on how to express that last bit in DAX.
Here my attempt:
Overdue Invoices =
CALCULATE(
SUM( Duplicatas[Value] ),
FILTER( ALL( Dates[Date] ), Dates[Date] = MAX( Dates[Date] )
),
USERELATIONSHIP( Duplicatas[Current Due Date], Dates[Date] ),
Duplicatas[State] = "approved",
Duplicatas[Paid Date] = BLANK()
)
Would be grateful if anybody could point out to me what I am doing wrong!
Many thanks!
Solved! Go to Solution.
Check the DAX below.
Measure = CALCULATE ( SUM ( Duplicatas[Value] ), FILTER ( ALL ( Duplicatas ), Duplicatas[State] = "approved" && Duplicatas[Due Date] < MIN ( Dates[Date] ) && ( Duplicatas[Paid Date] = BLANK () || Duplicatas[Paid Date] > MAX ( Dates[Date] ) ) ) )
Thanks a lot! The following ended up working:
(R$) Overdue Invoices = CALCULATE ( SUM ( Duplicatas[Value] ), FILTER ( ALL ( Duplicatas ), Duplicatas[State] = "approved" && Duplicatas[Current Due Date] < MIN( MAX( Dates[Date] ), TODAY() ) && ( Duplicatas[Paid Date] = BLANK () || Duplicatas[Paid Date] > MAX ( Dates[Date] ) ) ) )
You just need to use the measure below.
Measure = CALCULATE ( SUM ( Duplicatas[Value] ), USERELATIONSHIP ( Duplicatas[Due Date], Dates[Date] ), Duplicatas[State] = "approved", Duplicatas[Paid Date] = BLANK () )
Thanks a lot @v-chuncz-msft!
This formula works in parts:
It shows which invoices were due in each month that still haven't been paid. However, in October, it shows invoices that are still current (e.g. due only on Oct-30) as well.
What I am looking for is, at any given Dates[Date], the total value of invoices where the Duplicatas[Due Date] < Dates[Date].
Basically like this:
Note: I am duplicating the SUMIFS because there are two ways an invoice can be over due: Paid Date is empty (invoice hasn't been paid), or Paid Date is > Due Date (invoice was paid late, i.e. was overdue at some point).
Check the DAX below.
Measure = CALCULATE ( SUM ( Duplicatas[Value] ), FILTER ( ALL ( Duplicatas ), Duplicatas[State] = "approved" && Duplicatas[Due Date] < MIN ( Dates[Date] ) && ( Duplicatas[Paid Date] = BLANK () || Duplicatas[Paid Date] > MAX ( Dates[Date] ) ) ) )
Thanks a lot! The following ended up working:
(R$) Overdue Invoices = CALCULATE ( SUM ( Duplicatas[Value] ), FILTER ( ALL ( Duplicatas ), Duplicatas[State] = "approved" && Duplicatas[Current Due Date] < MIN( MAX( Dates[Date] ), TODAY() ) && ( Duplicatas[Paid Date] = BLANK () || Duplicatas[Paid Date] > MAX ( Dates[Date] ) ) ) )