Forum Discussion
DAX Measure - Current Week Amount
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.
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 |