Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
greenawayr
Helper I
Helper I

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

1 ACCEPTED SOLUTION

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

7 REPLIES 7
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Sorry, I'm not having much luck with this, I may not be explaining it properly.

 

My Staging table could contain multiple rows child rows and therefore I need a way to return the current score, only for the first row that matches my Parent ID, is older than 30 days and sorted in ascending order.

 

I've created a column that calculates the number of days between todays date and the modified date, and gives me an integer, so in theory I'm looking for the score where ID = Parent and && CalculatedModifiedDate > 30. However, because this returns more than one value, I'm getting errors around returning multiple results in a scalar value. I've TopN, Min etc, but keep seeming to get the same error.

 

How do I get only one result returned so I can store that in a column and subtract it from my LIVE score?

Thanks for your help so far.

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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

amitchandak
Super User
Super User

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.