Cumulative Total from A Count of Non Numerical Data (String)

[Picklingjeff]

Hello everyone. I'm a new user here, and struggling to get to grips with things.

My data is a list of events by date. The events are categorized into 16 categories. In my report I was previously using TOTALYTD quite successfully to do what I want. I set the base value to 'count of the value' and then I could see the number of cases build up over time by each category. However, at the start of January the total reset and I don't want it to do that.

I don't mind if it didn't reset at all, the the cumulative total just kept increasing. Then I could filter it for the period of time I am interested in.

Even better would be a similar TOTALYTD except the roll over occurred in August and not January.

I really don't know and would appreciate any help. Thanks in advance.

[parry2k · Accepted solution]

@Picklingjeff assuming you have date dimension in your model, if not then you should add one, check my post here.

your dax measure would be:

Cumulative Total = 
CALCULATE ( COUNTROWS ( Table ), FILTER ( ALL ( DateTable[Date] ), DateTable[Date] <= MAX ( DateTable[Date] ) ) )

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Picklingjeff]

Thanks so much for all your quick responses.

@parry2k I do have a calendar table in my model.

I tried your solution and it works much better than I could get, however my issue is that the total runs until the end of 2021. I would like the latest date be the latest date with information. 

So you can see that after the current date there is nothing adding to the total so the values run constant until Dec2021.

Is this a case that through my trials and messing with it, I have added dates in my calendar table until the end of the year? Can I just simply delete the rows? Is the MAX(DateTable[Date]) contain entries that go beyond the current date?

@amitchandak I took a look at your solution. Thanks for that. However, my data is not numerical and so I cannot SUM anything. I tried before doing a few ways to use that and SUMX but I kept getting the same error that the value is a string.

[Picklingjeff]

I figured out my own problem eventually. I have future/past as a flag in my calendar table so I could filter the visual. I also learned how to include it in a measure as well thanks to Guy In A Cube.

Thanks everyone. Solved!

[amitchandak]

@Picklingjeff , Try a measure like

Cumm Sales = CALCULATE(SUM(Table[value]),filter(allselected(Table),Table[Date] <=max(Table[Date]) && Table[category] =max(Table[category])))