I’m trying to develop a Cash Status dashboard.
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)