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

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