Forum Discussion
DAX Measure - Current Week Amount
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 |
Hi Anonymous
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 XuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
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)
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 TableAS 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 ResultCustomer Current Date Previous Date A 0 28,685 B 0 5,877 C 500 1,000 D 3,000 0 E 0 0
Hi Anonymous
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 XuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.