Cumulative total in stacked bar graph

[Anonymous]

Hello,

I'm trying to create a graph that adds the cumulative total (new users) of a column over each month and moreover to split each bar by categorie of Entity. The data set is from August 2020 until now.

For now I used the cumulative yearly in the formulas given by PowerBI and I got this:

With the measure: 

Cumulative year to date of Number of User =

IF(

    ISFILTERED('UserSinceTable'[User since (MMYYYY)]),

    ERROR("Time Intelligence quick metrics can be grouped or filtered only by the date hierarchy or main date columns provided by Power BI."),

    TOTALYTD([Number of User], 'UserSinceTable'[User since (MMYYYY)].[Date])

)

The problem is that it resets at the beginning of each year and I'm searching a continuous accumulation over several years like that: 

Any help would be appreciated, thank you!

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

Please check the below picture and the sample pbix file's link down below.

User count cumulate =

CALCULATE (

[users count],

FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] <= MAX ( 'Calendar'[Date] ) )

)

https://www.dropbox.com/s/r5r11eayejyni96/minhhai.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Anonymous]

Hi, thanks for the quick reply.

When I used your solution, I got this: 

with this formula:

Cumulate =

CALCULATE (

[Number of User],

FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] <= MAX ( 'Calendar'[Date] ) )

)

and Number of User = COUNT('Contact List'[User since (MMYYYY)])

I didn't use COUNTROWS(Data) here because several raws don't have to be count in my excel file.

Do you know how to correct this please.

Regards

Frédéric

[Jihwan_Kim]

Hi, @Anonymous 

Thank you for your feedback.

Please share your sample pbix file's link here, then I can try to look into it to come up with a more accurate measure.

Thank you.

[RaviKanth]

Here is my data file screenshot.  

[RaviKanth]

Hi Jihwan Kim, when I used your solution, I am still not getting the cumulative stacked bar correctly. When I don't include category in Legends section, cumulative is coming up correctly. But, as soon as I add "Category" field, stacked bar is breaking.

[normanhs66]

I am facing the same issue too. May I know how can we resolve this issue?

[Anonymous]

Hello, @Jihwan_Kim ,

It finally worked! 

Thank's so much

Frédéric

[Anonymous]

Here is my sample data:

Frédéric