Forum Discussion

DmitryKo's avatar
DmitryKo
Icon for Helper IV rankHelper IV
4 years ago

Latest value by date and key

Sample dataset:

DateKeyValue
04.08.2022A3
07.08.2022A7
05.08.2022B11
07.08.2022B13


The dates are NOT sequental; this is important as this assumption is freely taken in many similar cases. Dates could be anything, just random.

The goal is to be able to produce a measure showing average value as last known at any given date. Example result set with this measure would be:

DateKey"Latest known value"
04.08.2022A3
04.08.2022B(null)
05.08.2022A3
05.08.2022B11
06.08.2022A3
06.08.2022B11
07.08.2022A7
07.08.2022B13


At the same time, it should produce correct averages when grouped without the key

Date (from calendar table)"Latest known value" (average)Comment
04.08.20223(3+null)/1
05.08.20227(3+11)/2
06.08.20227(3+11)/2
07.08.202210(7+13)/2


If it wasn't for the Key, I could have used this solution (CT is my calendar table and Msr is my AVERAGE(Value) measure):

// VAR CurrentContextKeyValue = ???

VAR LastTimeHadValue = MAXX(
    FILTER(ALL(Tbl),
    Tbl[Date] < MAX(CT[Date])
//  && Tbl[Key] == CurrentContextKeyValue
    ),
    Tbl[Date]
)
VAR Last_Instance_Of_Calculated_Measure = CALCULATE([Msr],FILTER(ALL('Tbl'), Tbl[Date] = LastTimeHadValue
// && Tbl[Key] == CurrentContextKeyValue
))
RETURN Last_Instance_Of_Calculated_Measure

However, this returns average over all values and is not key-aware. To make it key-aware, I have to add filters similar to commented lines. But how to get filter context to get the CurrentContextKeyValue?

16 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    4 years ago

    Hi, 

    I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

     

     

     

     

     

    • DmitryKo's avatar
      DmitryKo
      Icon for Helper IV rankHelper IV
      4 years ago

      Hello and thanks for the quick reply.

      I'm afraid that the following logic in your metric uses assumption that dataset is ordered - LASTNONBLANK. The documentation says nothing on how exacly "last" value is taken. First/last logic assumes sequence in the dataset; how does LASTNONBLANK determine order of search?




  • DmitryKo's avatar
    DmitryKo
    Icon for Helper IV rankHelper IV
    4 years ago

    Let me actually narrow down the question. This code:

     

    VAR LastTimeHadValue = MAXX(
    FILTER(
        ALLEXCEPT(Tbl, Tbl[Key]),
        Tbl[Date] <= MAX(CT[Date])
    ),
    Tbl[Date]
)

     

    returns incorrect value over the sample above. It just returns the last date when any key had a value, thus ignoring the supposed logic of ALLEXCEPT(). I thought that ALLEXCEPT() over a table would keep the context filters related to the columns specified in 2nd argument - why isn't it doing so?


  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi DmitryKo 

    please try the folliwing for both visuals

     

    Latest known value =
AVERAGEX (
    VALUES ( TableName[Key] ),
    CALCULATE (
        MAXX (
            FILTER (
                CALCULATETABLE ( TableName, ALLEXCEPT ( TableName, TableName[Key] ) ),
                TableName[Date] <= MAX ( TableName[Date] )
            ),
            TableName[Value]
        )
    )
)

     

    • DmitryKo's avatar
      DmitryKo
      Icon for Helper IV rankHelper IV
      4 years ago

      As I mentioned in the initial message, the dates in the fact table are not sequential. Thus, this code only results in non-null values for dates representing non-null dates in the fact table; so it doesn't return latest known value at any given date.

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        4 years ago

        Hi DmitryKo 

        if you tried the code please update me with the results that you are getting compared with your expectation. 
        I don't actually understand the meaning of sequential dates as the power bi columnar data engine applies no index on the data hence, the order or the rows is absolutely irrelevant unless you want to calculate something based on the order of the data as it appears. If this the case then an index column must be added using power query to help the engine understand the order of the data. 

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi DmitryKo 
    Is the following good enough? Only problem when slicing by Key the record 04.08.2022 - B - (null) does not appear. I guess it can be solved but with extra DAX thus trying to avoid unnecessary extra code.

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi DmitryKo 
    Attached sample file with the solution

     

    Latest known value = 
VAR Result = 
    AVERAGEX (
        CALCULATETABLE ( VALUES ( Tbl[Key] ), ALL ( CT ) ),
        CALCULATE (
            VAR CurrentDate =
                MAX ( CT[Date] )
            VAR SelectedDates =
                FILTER ( ALLSELECTED ( CT[Date] ), CT[Date] <= CurrentDate )
            VAR MsrTable =
                FILTER ( 
                    ADDCOLUMNS ( 
                        SelectedDates, 
                        "@MSR", 
                        CALCULATE ( AVERAGE ( Tbl[Value] ) ) 
                    ), 
                    [@MSR] <> BLANK () 
                )
            VAR LastNonBlankRecord =
                TOPN ( 1, MsrTable, [Date] )
            RETURN
                MAXX ( LastNonBlankRecord, [@MSR] )
        )
    ) 
RETURN
    IFERROR ( VALUE ( Result & "" ), "" )

     

    • DmitryKo's avatar
      DmitryKo
      Icon for Helper IV rankHelper IV
      4 years ago

      Hello.

      This one seems to be working correctly, although I'd expect for it to be working out of the box with the simple ALLEXCEPT() function.

      I would have to check how it works in case of more than one dimension, but I guess adding another VALUES(Dimension2[Key]) should solve it.

      UPDATE: over a very small dataset (single dimension, ~1000 unique keys, less than 500 rows in the fact table spanning around 2 months total and around 100 keys of that single dimension) this measure's performance turned out to be extremely slow. I had a +12GB jump in memory usage and around 5 minutes of 100% CPU utilization after trying to open the report in the browser.

      The only difference was that metric added.

       

      Surely this is not a solution for any real data set (dozens of millions of rows of facts, 10-20 dimensions, several years timeframe span).

  • DmitryKo's avatar
    DmitryKo
    Icon for Helper IV rankHelper IV
    4 years ago

    Why would a random third party come and mark an INCORRECT reply here as an "accepted solution"? I've explained in details why proposed solution doesn't actually solve anything as it cannot be applied anywhere outside of a sandbox containing super small dataset.

    Forum mods, you really need to change your policy and disallow anyone but OP to mark as solution