Forum Discussion
Helper IIIshow last value on or before selected date
Hi
I have a table that contains daily balances for several accounts by date. It doesn't have an entry for every date.
The user selects a date they are interested in, I would like to display each account by value on a Clutered bar chart.
I am trying to create a measure that will calculate a value. If the an entry doesn't exist for the selected date I would like the value of last entry before the selected date.
This is what I have so far but is giving me the error max has been used in a true/false expression that is used as a table filter
[Total Value] is a measure I have created and just totals the closing balance field.
here is my model
thank you for all help
Here is one approach to do it. I made a mock table called Table that doesn't have balances for every date, and a Date table that has all dates.
Latest Day Balance = var selecteddate = SELECTEDVALUE('Date'[Date])var maxbalancedate = CALCULATE(MAX('Table'[BalanceDate]), ALL('Date'[Date]), 'Table'[BalanceDate]<=selecteddate)return CALCULATE([Total], 'Date'[Date]=maxbalancedate)If this works for you, please mark it as the solution. Please let me know if any questions.Regards,Pat
4 Replies
- mahoneypat
Microsoft Employee
Here is one approach to do it. I made a mock table called Table that doesn't have balances for every date, and a Date table that has all dates.
Latest Day Balance = var selecteddate = SELECTEDVALUE('Date'[Date])var maxbalancedate = CALCULATE(MAX('Table'[BalanceDate]), ALL('Date'[Date]), 'Table'[BalanceDate]<=selecteddate)return CALCULATE([Total], 'Date'[Date]=maxbalancedate)If this works for you, please mark it as the solution. Please let me know if any questions.Regards,Pat- mahoneypat, jasemilly...
The solution you gave and accepted is not correct if you have different accounts and the last date recorded for each account can be different.
Best
D I get the following error when attrmpting this:
The value for 'Total' cannot be determined. Either the column doesn't exist, or there is no current row for this column.
Do I have something in the wrong location or am I missunderstanding something?
- Please refer to this article to learn how to properly calculate balances:
https://www.sqlbi.com/articles/semi-additive-measures-in-dax/
For other common patterns, you can always use www.daxpatterns.com.
Best
D
