Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello
I have 3 columns :
Index (1,2,3,4,5,..,365)
Days (1May, 2 May, ..., Apr 30) - 365days
Month(May2017, May 2017, ,..., April 2018) - 365
Numbers (5454,2187,15487,48797,...,26554)
I want the find the difference, like (in index)
1-1= 0
2-1=2187-5454
3-2= 15487-2187
and so on
i found a Dax script:
Diff =
VAR Index = 'Table'[Index]
VAR Reference = 'Table'[Month].[Day]
VAR PrevCreditP =
CALCULATE (
FIRSTNONBLANK ( 'Table'[Numbers], TRUE () ),
FILTER ( 'Table', 'Table'[Index] = Index - 1 && 'Table'[Month].[Day] = Reference )
)
RETURN
IF (
ISBLANK ( PrevCreditP ),
BLANK (),
'Table'[Credit Provision] - PrevCreditP
)
But it works not properly. Despite the correct numbers in each columns ( in power bi and excel), their sum - are different.
Kindly ask to help me
Solved! Go to Solution.
Hi @leylarm,
Please create a calculated column with the formula below.
diff = VAR current_index = 'Table'[Index] VAR prior = CALCULATE ( SUM ( 'Table'[Numbers] ), FILTER ( ALL ( 'Table' ), 'Table'[Index] = current_index - 1 ) ) RETURN 'Table'[Numbers] - IF ( ISBLANK ( prior ), 'Table'[Numbers], prior )
Or measure with the foemula below.
Measure = VAR current_index = MAX ( 'Table'[Index] ) VAR prior = CALCULATE ( SUM ( 'Table'[Numbers] ), FILTER ( ALL ( 'Table' ), 'Table'[Index] = current_index - 1 ) ) RETURN MAX ( 'Table'[Numbers] ) - IF ( ISBLANK ( prior ), MAX ( 'Table'[Numbers] ), prior )
Here is the result.
In addition, you could refer to the similar thread.
Best Regards,
Cherry
Hi @leylarm,
Please create a calculated column with the formula below.
diff = VAR current_index = 'Table'[Index] VAR prior = CALCULATE ( SUM ( 'Table'[Numbers] ), FILTER ( ALL ( 'Table' ), 'Table'[Index] = current_index - 1 ) ) RETURN 'Table'[Numbers] - IF ( ISBLANK ( prior ), 'Table'[Numbers], prior )
Or measure with the foemula below.
Measure = VAR current_index = MAX ( 'Table'[Index] ) VAR prior = CALCULATE ( SUM ( 'Table'[Numbers] ), FILTER ( ALL ( 'Table' ), 'Table'[Index] = current_index - 1 ) ) RETURN MAX ( 'Table'[Numbers] ) - IF ( ISBLANK ( prior ), MAX ( 'Table'[Numbers] ), prior )
Here is the result.
In addition, you could refer to the similar thread.
Best Regards,
Cherry
Thank you very much!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |