Forum Discussion

greenawayr's avatar
greenawayr
Helper I
6 years ago
Solved

Variance between rows if multiple entries exist in specified timeframe

Hi,

 

Fairly new to PowerBI here so sorry if this is something that should be straightforward.

 

I have a parent table that has Unique ID for each row.

 

I have a child table that gets written to by an app every time a record is modified. What the modification is could be any of the fields.

 

My requirement is to be able to check through the child table for each parent entry, and identify the variance between the "Current Score" in the most recent item and "Current Score" in any previous item with the same foreign key that falls within the time period specified.

 

e.g.

Foreign Key IDDateCurrent Score
11/1/202015
118/12/201910
120/11/20198
125/10/201911

 

So depending on the date range selected by the user I could return a variance between the current score of 15 and any of the other scores associated with that ID.

 

Hope this makes sense.

 

Thanks

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi greenawayr,

    In fact, I already filter on the 'parent' field to find out the minimum date based on its 'id' and stored into a variable.
    After these steps, you can use current id and the first date to lookup the correspond score and calculate with the current score to get the diff.

    Diff =
VAR currScore =
    MAX ( LIVE[Score] )
VAR currID =
    MAX ( LIVE[ID] )
VAR currDate =
    MAX ( LIVE[Modified] )
VAR prevDate =
    CALCULATE (
        MIN ( Staging[Modified] ),
        FILTER (
            ALLSELECTED ( Staging ),
            [ParentID] = currID
                && [Modified] <= currDate - 30
        )
    )
VAR prevScore =
    LOOKUPVALUE (
        Staging[Score],
        Staging[ParentID], currID,
        Staging[Modified], prevDate
    )
RETURN
    IF ( prevDate <> BLANK (), currScore - prevScore, 0 )

    BTW, My formula is a measure, please not use it in a calculated column.

    Regards,

    Xiaoxin Sheng

7 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    One way to get it in the table having the primary key is

     

     Max date in Table 1 = maxx(filter(table2,table1[Primary Key] = table2[Foreign Key ID] ,table[Date])
 Score in Table 1 = maxx(filter(table2,table1[Primary Key] = table2[Foreign Key ID] &&  table1[Max date in Table 1] = table2[Date],table[Date])

     

    Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
    Thanks. My Recent Blog -
    Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
    Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

    Connect on Linkedin

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    HI greenawayr ,

    I'd like to suggest you use parent key id to lookup related detail records, then you can do variance calculation by VARX.P VARX.S functions. BTW, can you please share the parent table structure to help us clarify your requirement for test? 

    How to Get Your Question Answered Quickly 
    Regards,

    Xiaoxin Sheng

    • greenawayr's avatar
      greenawayr
      Helper I
      6 years ago

      Thanks,

       

      So I'm dealing with a "LIVE" table and a "Staging" table. So the schemas are much the same, but the live table only holds one row per record and the staging table holds a record for each modifcation made to the parent record.

       

      So LIVE

      IDMODIFIEDScore
      101/22/202015
      201/22/20208
      301/20/202012

       

      and Staging

      RowIDParentIDModifiedScore
      6201/22/20208
      5101/22/202015
      4301/20/202012
      3201/10/202012
      2212/20/201911
      1112/05/201913

       

      So for the LIVE table, ID 2, it's current score is 8, but 30 days ago it was 11 (we ignore the score change that made it 12, we just want the score as it was 30 days ago) and the variance is -3.

       

      Hope this helps. Thanks

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi greenawayr,

        You can try to use the following measure formula to get the diff between the current 'live' score and filtered first 'staging' score based on 'ID' group:

        Diff =
VAR currScore =
    MAX ( LIVE[Score] )
VAR currID =
    MAX ( LIVE[ID] )
VAR currDate =
    MAX ( LIVE[Modified] )
VAR prevDate =
    CALCULATE (
        MIN ( Staging[Modified] ),
        FILTER ( ALLSELECTED ( Staging ), [ParentID] = currID )
    )
VAR prevScore =
    LOOKUPVALUE (
        Staging[Score],
        Staging[ParentID], currID,
        Staging[Modified], prevDate
    )
RETURN
    currScore - prevScore

        Regards,

        Xiaoxin Sheng