Forum Discussion

miguelmkuk's avatar
miguelmkuk
Frequent Visitor
3 years ago
Solved

Last Values with contextual filters

Hello,

 

I have a set of data summarised as below:

 

DateAccountBalance
26/03/2023B50
12/03/2023D10
26/02/2023C500
12/02/2023B200
29/01/2023D100
15/01/2023C1000
01/01/2023B400
18/12/2022D50
04/12/2022C10
11/09/2022C500
28/08/2022D200
14/08/2022A100
31/07/2022C1000
08/05/2022D400
24/04/2022B50
10/04/2022A10
27/03/2022C500
13/03/2022D200
27/02/2022D100
13/02/2022D1000
30/01/2022B400
07/11/2021B50
12/09/2021A10
29/08/2021C500
15/08/2021B200
01/08/2021A100


"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

 

4 Replies

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    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. 

     

  • miguelmkuk's avatar
    miguelmkuk
    Frequent Visitor
    3 years ago

    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

  • miguelmkuk's avatar
    miguelmkuk
    Frequent Visitor
    3 years ago

    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!