Cumulative total with several legends

[SJHA]

Hello, I've been trying to create a cumulative total of counts of different types by month. I've have 3 columns:

- Date (when it was created)

- Name of type (There are 4 types in total)

- Count (counting 1 for each row)

I would like to create a diagram where it shows the total count by month.

So, it should be going up steadily, but it doesn't...

I've been trying with this for the cumulative total: 

Cumulative Total =

CALCULATE(SUM('WorkPoint faser'[Antal]),
FILTER(ALL('WorkPoint faser'[Oprettet]), 'WorkPoint faser'[Oprettet] <= MAX('WorkPoint faser'[Oprettet])))

I can't seem to get it to add month by month for different types. Can someone help?

Thank you

[Jihwan_Kim]

Hi, @SJHA 

Please correct me if I wrongly understood your question.

I am not sure about your data model, but I think you are not using Dim-Date-Table.

If you only use your fact table, and if you try to create a measure using ALL function, it will be complicated to create a cumulate total per type.

Please try to have a Dim-Calendar Table, and then try to write the below measure.

The sample pbix file's link is down below.

count cumulate =
CALCULATE (
SUM ( Data[Count] ),
FILTER (
ALLSELECTED ( 'Calendar'[Date] ),
'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
)
)

https://www.dropbox.com/s/kgf9mvu9a2jqa3x/sjha.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: https://www.linkedin.com/in/jihwankim1975/

[SJHA]

Hi, I just tried using a dim date table:

This is the result when I'm using a DIM date table

These are my columns: Antal = count and a legend column (faser_simpel)

[Jihwan_Kim]

Hi, @SJHA 

Thank you for your feedback.

Sorry to say that it is quite difficult for me to look into the problem by seeing the screencapture. 

If it is OK with you, please share your sample pbix file, then I can try to write more accurate measures.

Thank you.

[SJHA]

I think the problem may be that my counts are just 1s.

data file

https://www.dropbox.com/s/pl16j9fbnz69y25/count%20cumulate%20by%20M%C3%A5ned%20and%20Faser_simpel.cs...

pbix file with the data

https://www.dropbox.com/s/7qly650zghtjupx/sjha.pbix?dl=0d%20Faser_simpel.csv?dl=0

[Jihwan_Kim]

Hi, @SJHA 

Thank you for your sample file.

Please change the data type to date.

The column, Oprettet, is not Date-Type, and it is not properly connected to the dim-date-table.

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: https://www.linkedin.com/in/jihwankim1975/

[SJHA]

https://www.dropbox.com/s/fwpwwzkxgu8zch1/WPA_data_test.pbix?dl=0

This should be correct. I'm sorry the previous example wasn't created correctly with the dates.

[Jihwan_Kim]

Hi, @SJHA 

Thank you for your information.

However, your date table is not a date table.

It has a gap in the table, so it cannot be assigned as a date table.

Please create it again, and make it as a date table.

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: https://www.linkedin.com/in/jihwankim1975/

[SJHA]

Hi again @Jihwan_Kim 

You said that my "...date table is not a date table.

It has a gap in the table, so it cannot be assigned as a date table.

Please create it again, and make it as a date table."

I don't quite understand what you mean as it works fine from my end when I convert to date datatype - is it a calendar/time settings differences?

This is the data https://www.dropbox.com/scl/fi/7q43ivevh16dl8nnep45g/data_example.xlsx?dl=0&rlkey=y0f121u3gqdebgdxtl...

I would like to know how many of the items are in the stages by month and let it be cumulative.

Would it be possible to get it in percentage per month as well? The percentage of items in stage "Udførelser" out of the total items by the month.

I've been stuck for many hours trying to get it right, so I really appreciate your help here.

Thank you

[Jihwan_Kim]

Hi,

I think you missed one column in the excel file.

There are only Title, Created, Stage.

No column for the numbers.

Please kindly check and re-share the link.

[SJHA]

They count for 1 each, but I've updated the Excel document as well https://www.dropbox.com/scl/fi/7q43ivevh16dl8nnep45g/data_example.xlsx?dl=0&rlkey=y0f121u3gqdebgdxtl...

[Jihwan_Kim · Accepted solution]

Hi, @SJHA 

Please check the link down below.

All measures are in the sample pbix file.

Regarding cumulative measures, it is starting from the year 2019 and there is no reset per each year. If you want to reset per each year, then you can simply replace ALL to ALLSELECT.

Please check and let me know whether it is what you are looking for.

https://www.dropbox.com/s/3wt4dc5i2tjjnbe/SJHA%20v2.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: https://www.linkedin.com/in/jihwankim1975/

[SJHA]

Thanks a lot for the file. I've learned a couple og things or two already!

Your help is much appreciated.

[Jihwan_Kim]

Hi, @SJHA 

Please check the below picture.

I will use your excel file to create the visual. Please wait.