topic Re: DAX Measure - Current Week Amount in DAX Commands and Tips

DAX Measure - Current Week Amount

Anonymous — Thu, 28 Mar 2024 13:58:01 GMT

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

Re: DAX Measure - Current Week Amount

lbendlin — Thu, 28 Mar 2024 20:50:28 GMT

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)

Re: DAX Measure - Current Week Amount

Anonymous — Thu, 28 Mar 2024 21:19:05 GMT

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

Re: DAX Measure - Current Week Amount

lbendlin — Thu, 28 Mar 2024 22:38:13 GMT

Re: DAX Measure - Current Week Amount

Anonymous — Mon, 01 Apr 2024 09:38:14 GMT

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 Xu

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