Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi there,
Here is a simple view of the sample data
I want to create a measure in PBI that for account B in Month 2 I divide that by Account A in Month 1 (which equals 2). Then i want to carry that on for month 3 and divide 300/200 (Account C month 3 by Account B month 2).
I have been scratching my head how to figure this out. Can anyone please help?
Thanks!
GZ
Solved! Go to Solution.
hi, @Anonymous
Just try this formula:
Measure 3 = VAR _lastvalue = CALCULATE ( MAX ( 'Table'[Month] ), FILTER ( ALL ( 'Table' ), 'Table'[Month] < MAX ( 'Table'[Month] ) ) ) RETURN DIVIDE ( CALCULATE ( SUM ( 'Table'[Value] ) ), CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( ALL ( 'Table' ), 'Table'[Month] = _lastvalue ) ) )
Result:
here is pbix file, please try it.
Best Regards,
Lin
hi, @Anonymous
If so, you need to add an index/rank column for Account column to determine which Account is prior of the same month.
Otherwise how do you know the prior account of "B" is "A" not "D".
So adjust the formula as below:
Step1:
Create an index/rank column for Account column.
Step2:
Measure 3 = VAR _lastvalue = CALCULATE ( MAX ( 'Table'[Month] ), FILTER ( ALL ( 'Table' ), 'Table'[Month] < MAX ( 'Table'[Month] ) ) ) var _lastAccount= CALCULATE ( MAX ( 'Table'[Account] ), FILTER ( ALL ( 'Table' ), 'Table'[Index] < MAX ( 'Table'[Index]) ) ) RETURN DIVIDE ( CALCULATE ( SUM ( 'Table'[Value] ) ), CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( ALL ( 'Table' ), 'Table'[Month] = _lastvalue &&'Table'[Account]=_lastAccount) ) )
Result:
here is new pbix file, please try it.
Best Regards,
Lin
hi, @Anonymous
Just try this formula:
Measure 3 = VAR _lastvalue = CALCULATE ( MAX ( 'Table'[Month] ), FILTER ( ALL ( 'Table' ), 'Table'[Month] < MAX ( 'Table'[Month] ) ) ) RETURN DIVIDE ( CALCULATE ( SUM ( 'Table'[Value] ) ), CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( ALL ( 'Table' ), 'Table'[Month] = _lastvalue ) ) )
Result:
here is pbix file, please try it.
Best Regards,
Lin
Hi there,
This works for the smaller data set but if i expand it a bit it doesn't. The issue is that the account changes. Please see below when I use your measure. See below on what the measure looks like if i expand the data set.
The calculation should actually be the below (this one done in excel)
Thanks for your help!
hi, @Anonymous
If so, you need to add an index/rank column for Account column to determine which Account is prior of the same month.
Otherwise how do you know the prior account of "B" is "A" not "D".
So adjust the formula as below:
Step1:
Create an index/rank column for Account column.
Step2:
Measure 3 = VAR _lastvalue = CALCULATE ( MAX ( 'Table'[Month] ), FILTER ( ALL ( 'Table' ), 'Table'[Month] < MAX ( 'Table'[Month] ) ) ) var _lastAccount= CALCULATE ( MAX ( 'Table'[Account] ), FILTER ( ALL ( 'Table' ), 'Table'[Index] < MAX ( 'Table'[Index]) ) ) RETURN DIVIDE ( CALCULATE ( SUM ( 'Table'[Value] ) ), CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( ALL ( 'Table' ), 'Table'[Month] = _lastvalue &&'Table'[Account]=_lastAccount) ) )
Result:
here is new pbix file, please try it.
Best Regards,
Lin
User | Count |
---|---|
79 | |
74 | |
62 | |
61 | |
45 |
User | Count |
---|---|
108 | |
98 | |
89 | |
82 | |
61 |