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

Anonymous's avatar
Anonymous
Not applicable
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

greenawayr's avatar
greenawayr
Helper I
6 years ago

Hi,

 

Sorry, I marked this as the correct answer by mistake.

I think the problem here is the solution given is a measure.

 

I need to see the variance from the "Current Score" in Live, to the Current Score of the child item in the staging table that is the Max modified date but older than 30 days.

 I'm trying the below, which seems to be performing the correct calculation as there are Variances being shown where they exist, however it's not pulling back the correct variance as I can see calculations happening which include values that weren't last score 30 days ago.

ColScoreDiff = 
VAR prevDate = CALCULATE(MAX(Risks_Staging[Modified]),FILTER(ALLSELECTED(Risks_Staging),Risks_Staging[iHubRef]=Risks_LIVE[iHubRef] && Risks_Staging[Modified] <= TODAY() - 30))
VAR prevScore = LOOKUPVALUE(Risks_Staging[CurrScore],Risks_Staging[iHubRef],Risks_LIVE[iHubRef],Risks_Staging[Modified], prevDate)
Return
IF(prevDate<> BLANK (), Risks_LIVE[CurrScore] - prevScore, 0)

 

0123217/12/201916
0123202/11/20194

 

And in my parent table I would have

IDScoreVariance
01232168

In this case I wouldn't expect to see a variance as the Live score is the same as the score 30 days ago. However, in my DAX shown above it returns a value of -8, which shows that I'm looking up a value at least, but not quite with the correct parameters and I cannot figure out why.

 

Thanks again