I’ve put together a simple model which shows my problem.
I want to show the value of the last BAI code that = 45 for Acct =1.
The problem is that the last BAI code 45 for Acct 1 transaction took place on 1/2/2020, but the last date in the calendar table (not code 45) takes place on 1/3/2020. As a result the measure is returning blank, because the last date in the calendar table is 1/3/2020, but there aren’t any BAI code 45 transactions for Acct 1 on that day.
My measure is
Last Acct 1 BAI 45 = CALCULATE([Sum of Acct 1 BAI 45 Amount],FILTER(‘Calendar’,‘Calendar’[DayID]=MAX(‘Calendar’[DayID])))
I’m not sure how to get the correct coding. In my example the correct answer = 20.
I’m new to DAX, but my thinking was create a table that is filtered to only Acct =1,and BAI =45. Then filter that table down to the last date and return the resulting amount[.
To me that sounds like nesting FILTER functions, but when I tried, it didn’t seem to work. Also, I’m not sure if nesting FILTER functions is really the way to go.
Let me know if you need more clarification on the problem.
Test on Last Amount for Product B.pbix|attachment](upload://faq9RLLOKUwdwcAmeMH3pWIEyDz.pbix) (53.1 KB)
I’m attaching the working of my file for the reference.
If you’re following the New Date table code which was recently updated then in that case you can filter it out and have the actual value instead of blank.
You can try out this formula and check if it works for you -
Last Acct 1 BAI 45 =
IF( LASTDATE( Calendar[Date] ) > TODAY() ,
BLANK(),
CALCULATE([Sum of Acct 1 BAI 45 Amount],
FILTER(‘Calendar’,‘Calendar’[DayID] <= MAX(‘Calendar’[DayID])))`
Please Note: The naming convention might differ in your file so you can use it accordingly.
I’m also providing a link where similar sort of problem was faced by one of the member and got solved.
Hoping you find this useful and helps you in your analysis.
Apparently, all three of us jumped on this one at the same time, so you get 3 solutions for the price of one today. I always think it’s helpful to see different ways of achieving the same result, so in that spirit, here’s my measure:
Filtered Value =
VAR MaxDate =
CALCULATE(
MAX( Data[Date] ),
FILTER(
Data,
Data[Account] = 1 &&
Data[BAI Code] = 45
)
)
VAR Result =
CALCULATE(
MAX( Data[Amount] ),
FILTER(
Data,
Data[Account] = 1 &&
Data[BAI Code] = 45 &&
Data[Date] = MaxDate
)
)
RETURN
Result
Hi @BillK. Sorry, got cut off a couple of hours ago in mid post and had to withdraw; I didn’t get a chance to finish my solution until a few minutes ago. Min uses 2 measures as well
Max Date per Account and Code =
VAR CurrentAccount = SELECTEDVALUE( Transactions[Account] )
VAR CurrentCode = SELECTEDVALUE( Transactions[BAI Code] )
RETURN
MAXX(
FILTER( ALL( Transactions ),
Transactions[Account] = CurrentAccount &&
Transactions[BAI Code] = CurrentCode ),
Transactions[Date] )
Max Amount =
VAR CurrentDate = SELECTEDVALUE( Transactions[Date] )
RETURN
IF( CurrentDate = [Max Date per Account and Code], [Total Amount], BLANK() )
You can then display the Last Amount for any account/code combination in a grid, something like:
Hi @BillK, we hope that you are having a great experience using the Support Forum so far. We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!
I always think it’s helpful to see different ways of achieving the same result, so in that spirit, here’s my measure:
