Forum Discussion
Running total
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
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
Hi,
Just change the cross filter direction to Single. See image below:
7 Replies
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?
Hi,
Just change the cross filter direction to Single. See image below:
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
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?
