The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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
Proud to be a Super User!
Solved! Go to 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
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?
Proud to be a Super User!
Hi,
You may download my PBI file from here.
Hope this helps.
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?
Proud to be a Super User!
Hi,
Just change the cross filter direction to Single. See image below:
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.
thanks for your help. you soluition works as well.
Proud to be a Super User!
You are welcome. Thank you for your kind words.