DAX Measure - Current Week Amount

[dmeadway]

I have created a dax measure to calculate the Current Overdue Amount for each customer in a table. The measure is formulated as follows:

=VAR CurrDate = MAX('f_AR_Trend'[AS OF DATE])

VAR CurrOD = CALCULATE([Total Overdue], f_AR_Trend[AS OF DATE] = CurrDate)

RETURN

CurrOD

The issue I am having is this formula is returning the value of the PrevDate (i.e. If 3/17/2024 is the most recent "As Of Date" and 3/3/2024 is the next most recent "As Of Date") as part of the Current Overdue Amount measure. How do I write the formula to fill in a zero instead of the amount from 3/3 if a customer had on overdue amount on 3/3, but did not have any amount on 3/17?

The Table structure is as follows (simplified for the fields the measure uses):

AS OF DATE	Customer	Total Overdue
3/3/2024	A	28,685
3/3/2024	B	5,877
3/17/2024	A
3/17/2024	B

[lbendlin · Accepted solution]

[v-xuxinyi-msft · Accepted solution]

Hi @dmeadway 

Your solution is great, @lbendlin. It worked like a charm! Here I have another idea in mind, and I would like to share it for reference.

I created serval measures as follow.

AS = MAX([AS OF DATE])

rank = RANKX(ALLEXCEPT(Source, Source[Customer]), [AS], , DESC, Dense)

Current Date = 
VAR _total = CALCULATE(MAX([Total Overdue]), FILTER(Source, [AS OF DATE] = SELECTEDVALUE('Table'[Date])))
RETURN
IF(_total = BLANK(), 0, _total)

Previous Date = 
VAR _rank = MAXX(FILTER(Source, [AS OF DATE] = SELECTEDVALUE('Table'[Date])), [rank])
VAR _lastdate = CALCULATE(MAX([AS OF DATE]), FILTER(ALL(Source), [rank] = 2))
VAR _total = CALCULATE(MAX([Total Overdue]), FILTER(ALLEXCEPT(Source, Source[Customer]), [AS OF DATE] = _lastdate))
RETURN
IF(_total = 0, 0, MAX([Total Overdue]))

Result:

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Are you expecting to see 0 (zero)  for both accounts?  You can either fudge your measure with the "+0"  hack, or use COALESCE(measure,0)

[dmeadway]

The goal would be to have a zero return if a customer has no data for the current max date. Same concept for the previous.

Source Table

AS OF DATE	Customer	Total Overdue
3/3/2024	A	28,685
3/3/2024	B	5,877
3/3/2024	C	1,000
3/17/2024	C	500
2/25/2024	D	2,000
3/17/2024	D	3,000
2/20/2024	E	1,000
2/25/2024	E	1,500

Below is the output I would want to see. I want to compare the two most recent dates within my table for all customers in my data set. If it didn't have activity on the two most recent dates the output should be zero. If it had activity on the current, but not on the previous then the output should be the amount for the current and zero for previous and vice versa. 

End Result

Customer	Current Date	Previous Date
A	0	28,685
B	0	5,877
C	500	1,000
D	3,000	0
E	0	0

[lbendlin · Accepted solution]