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.
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!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
84 | |
83 | |
64 | |
60 | |
55 |
User | Count |
---|---|
183 | |
111 | |
105 | |
77 | |
70 |