show last value on or before selected date

[jasemilly]

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

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

[Anonymous]

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

[mahoneypat · Accepted solution]

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

[techno]

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?

[Anonymous]

@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