Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi All,
Below is my data sample
I want to have result like this
I use this formula that the accumulation didn't work
Any support on this, highly appriciated.
Solved! Go to Solution.
Hi @Torbuzz ,
If you want to get result as your question, I think you need to sort your table by [Date] and [Name].
Measure:
GrandTotal = CALCULATE(SUM('Table'[In]) - SUM('Table'[Out]))
SisaStok =
VAR _Step1 =
SUMMARIZE (
ALLSELECTED ( 'Table' ),
'Table'[Index],
Kalender[Year],
Kalender[MonthSort],
'Table'[Name],
"GrandTotal", [GrandTotal],
"Group",
MAXX (
FILTER (
ALLSELECTED ( 'Table' ),
[GrandTotal] < 0
&& 'Table'[Index] < EARLIER ( 'Table'[Index] )
),
'Table'[Index]
)
)
VAR _Step2 =
ADDCOLUMNS (
_Step1,
"RunningTotal",
IF (
[GrandTotal] < 0,
0,
SUMX (
FILTER (
_Step1,
[Group] = EARLIER ( [Group] )
&& 'Table'[Index] <= EARLIER ( 'Table'[Index] )
),
[GrandTotal]
)
)
)
RETURN
SUMX ( FILTER ( _Step2, [Index] IN VALUES ( 'Table'[Index] ) ), [RunningTotal] )
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Now I change the formula to be like this :
What should I do?
Hi @Torbuzz ,
If you want to get result as your question, I think you need to sort your table by [Date] and [Name].
Measure:
GrandTotal = CALCULATE(SUM('Table'[In]) - SUM('Table'[Out]))
SisaStok =
VAR _Step1 =
SUMMARIZE (
ALLSELECTED ( 'Table' ),
'Table'[Index],
Kalender[Year],
Kalender[MonthSort],
'Table'[Name],
"GrandTotal", [GrandTotal],
"Group",
MAXX (
FILTER (
ALLSELECTED ( 'Table' ),
[GrandTotal] < 0
&& 'Table'[Index] < EARLIER ( 'Table'[Index] )
),
'Table'[Index]
)
)
VAR _Step2 =
ADDCOLUMNS (
_Step1,
"RunningTotal",
IF (
[GrandTotal] < 0,
0,
SUMX (
FILTER (
_Step1,
[Group] = EARLIER ( [Group] )
&& 'Table'[Index] <= EARLIER ( 'Table'[Index] )
),
[GrandTotal]
)
)
)
RETURN
SUMX ( FILTER ( _Step2, [Index] IN VALUES ( 'Table'[Index] ) ), [RunningTotal] )
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.