Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jasemilly
Helper I
Helper I

show 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

 

 

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

 

DataModel.png

 

thank you for all help

 

1 ACCEPTED SOLUTION
mahoneypat
Employee
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
 




Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

4 REPLIES 4
Anonymous
Not applicable

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
Employee
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
 




Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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?

techno_0-1708432322356.png

 

Anonymous
Not applicable

@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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors