Last Values with contextual filters

[miguelmkuk]

Hello,

I have a set of data summarised as below:

Date	Account	Balance
26/03/2023	B	50
12/03/2023	D	10
26/02/2023	C	500
12/02/2023	B	200
29/01/2023	D	100
15/01/2023	C	1000
01/01/2023	B	400
18/12/2022	D	50
04/12/2022	C	10
11/09/2022	C	500
28/08/2022	D	200
14/08/2022	A	100
31/07/2022	C	1000
08/05/2022	D	400
24/04/2022	B	50
10/04/2022	A	10
27/03/2022	C	500
13/03/2022	D	200
27/02/2022	D	100
13/02/2022	D	1000
30/01/2022	B	400
07/11/2021	B	50
12/09/2021	A	10
29/08/2021	C	500
15/08/2021	B	200
01/08/2021	A	100

"Date" column, which is the date the value of column "Balance" changes/updates. The "Date" column is linked to the Calendar table in the data model.

"Account" column, which is the name of the account the "Balance" column relates to. The number and name of accounts is variable (new accounts will be created and existing accounts will be closed)

"Balance" column contains the values to be evaluated.

I want to create a measure that returns the balance in a given date (filtered by the calendar table) regardless the the set of data contains an entry for that date or not. If there is an entry, it's straight forward but if there isn't, the measure has to look for the last time the value was updated (maybe months or years ago) for the related account (other accounts might have been updated recently)

I've tried for many hours with commands such as LASTNONBLANK() but always get a lot of problems.

The contextual filters must still work as I will use the measure in a pivot table with data sorted by the Calendar dates and by the account names (and others)

Thank you very much.

Kind regards
Miguel

[miguelmkuk]

Hello,

I haven't managed to find the solution to this yet.

A ver similar thread I found in this forum: LASTNONBLANK Explained - Excelerator BI
but yet, they do not do the formula I need.

I've asked for help in that other forum also but no answer, if you can help in the meantime would be very much appreciated.

In that other forum example, the question would be:

" How should it be the formula if in addition to the current return values, I need to:

1. return values in those periods that there is no entry, i.e. to carry on the value from the previous period.
Example: May 2016, Kathy: 1,474 (rather than blank)

2. (Once point 1 is achieved) removing the “account” contextual filter so I can see the sum of all account balances at a moment in time.
e.g. balance for May 2016: 3,280 (1,474 from account “Kathy” and 1,806 from account “Matt”)

I’ve been trying to obtain this for many many hours and couldn’t manage to do it. I’m not an expert of DAX at all but couldn’t find anything clear about this despite I believe must be a usual application."

Thank you!

[miguelmkuk · Accepted solution]

Hello,

I solved my problem with the help of the following article:

https://exceleratorbi.com.au/fill-table-with-last-survey-result/

Then I needed to itterate the formula based on distinct values of the "Account" column. Chat GPT did this part for me!!

[miguelmkuk]

Thank you @FreemanZ

This is very good progress for me!

My main goal is to find the latest balance of any accounts for any given period/date.

I've implemented your advise as per screenshots below and displayed the results in a Pivot Table.
I've then copied the table next to it and add manually the additional results I'd like to get.

DAX Measure "Last Balance"

Relationship between Data[Date] and Calendard[Date]

"Last Balance" measure results in Pivot Table and comments about additional needs/modifications.

Hope it makes sense to you and can help me a bit more.

Thank you very much

Kind regards

miguelmkuk

[FreemanZ]

hi @miguelmkuk 

not sure if i fully get you. try to plot a visual with Data[Account] column and a measure like:

Measure = 
VAR _date= MAX(Dates[Date])
VAR _account = MAX(Data[Account])
VAR _table =
    FILTER(    
        ALL(Data),
        Data[Account]=_account
            &&Data[Date]<=_date
    )
VAR _maxdate =    
    MAXX(
        _table,
        Data[Date]
    )
VAR _balance =
    MAXX(
        FILTER(_table,Data[Date]=_maxdate),
        Data[Balance]
    )
RETURN 
    _balance

it worked like:

The Date slicer is with Dates[Date] column. 

You see, the point is identify the proper last date.