Running total

[ryan_mayu]

hi all,

I have a sample data.

ID	date	value
a	2021/1/1	100
a	2021/2/1	200
a	2021/4/1	300
a	2021/5/1	500
b	2021/3/1	600
b	2021/6/1	700
b	2021/7/1	800

and below is my expected output.

	Jan	Feb	Mar	Apr	May	Jun	Jul
a	100	300	300	600	1100	1100	1100
b			600	600	600	1300	2100

Thanks in advance

[Jihwan_Kim · Accepted solution]

Value cumulate : =
VAR _viewvalue =
CALCULATE ( MAX ( Data[date] ), REMOVEFILTERS () )
RETURN
IF (
HASONEVALUE ( 'Calendar'[Month Name] ),
IF (
MIN ( 'Calendar'[Date] ) <= _viewvalue,
CALCULATE (
SUM ( Data[value] ),
FILTER (
ALL ( 'Calendar'[Date] ),
'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
)
)
)
)

https://www.dropbox.com/s/fhitbnc2v1dnxrt/ryan.pbix?dl=0 

[Jihwan_Kim · Accepted solution]

Value cumulate : =
VAR _viewvalue =
CALCULATE ( MAX ( Data[date] ), REMOVEFILTERS () )
RETURN
IF (
HASONEVALUE ( 'Calendar'[Month Name] ),
IF (
MIN ( 'Calendar'[Date] ) <= _viewvalue,
CALCULATE (
SUM ( Data[value] ),
FILTER (
ALL ( 'Calendar'[Date] ),
'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
)
)
)
)

https://www.dropbox.com/s/fhitbnc2v1dnxrt/ryan.pbix?dl=0 

[ryan_mayu]

@Jihwan_Kim 

thanks for your help. I can't open your pbix file since my pbi version is not the latest one. I tried to use your method based on your screenshot. However, I can't get accumulated count. could you pls advise?

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[ryan_mayu]

@Ashish_Mathur 

thanks for your help. I changed the sum to count and it works on your pbix file. However, i can't get the same result on mine. could you pls help on this?

[Ashish_Mathur · Accepted solution]

Hi,

Just change the cross filter direction to Single.  See image below:

[ryan_mayu]

@Ashish_Mathur 

thanks for your help. This also solved the problem why I can't get the result which @Jihwan_Kim provided. just a littele change, the results are totally differrent. For me,still need to keep learning from experts like you.

@Jihwan_Kim 

thanks for your help. you soluition works as well.

[Ashish_Mathur]

You are welcome.  Thank you for your kind words.