topic Re: show last value on or before selected date in DAX Commands and Tips

show last value on or before selected date

jasemilly — Wed, 29 Apr 2020 14:41:07 GMT

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

LastValueAmmount =

CALCULATE(

[Total Value],

FILTER(

ALL('Statement Date'[Date]),

LASTNONBLANK('Statement Date'[Date],[Total Value])

)

, 'Statement Date'[Date] <= MAX ( 'Statement Date'[Date] )

)

[Total Value] is a measure I have created and just totals the closing balance field.

here is my model

thank you for all help

Re: show last value on or before selected date

mahoneypat — Wed, 29 Apr 2020 23:15:30 GMT

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

Re: show last value on or before selected date

Anonymous — Sun, 03 May 2020 11:07:06 GMT

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

Re: show last value on or before selected date

Anonymous — Tue, 05 May 2020 07:42:28 GMT

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

Re: show last value on or before selected date

techno — Tue, 20 Feb 2024 12:32:14 GMT

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?