Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |