Running Total Difficulties

[mschlesselman]

I am working on a unique application of a running total and having some significant difficulty.

I need to see the count of items by date across all levels, to be able to look back and see the progression over time. Additionally, Levels may not be added at regular intervals, and the counts may not be updated at regular intervals. I need to be able to get a distinct count of areas, retrieve the most recent data point for that area, and contribute it to the total sum of items. I have been able to calculate the max data for each category and contribute it to a sum, however when I do this, it fills in all my data points as that value, and I lose the ability to see the trend. Additionally, I have been able to see a trend, but it will only acknowledge max data points over the whole period of data, not the max data points below the currently evaluated time. I have tried storing the values in a date table with no luck as well.

The data set is rather large, so I created a smaller set of data to test the logic and functionality with, however I still have had no luck. I have the data I am using as well as the output I am trying to get. If you have any ideas or experience with a similar problem, I would greatly appreciate your help.

Thanks

[sturlaws · Accepted solution]

Hi @mschlesselman 

does it have to be done entirely by measure, or is it acceptable with an additional column? In the latter case, have a look at the attached .pbix-file

Cheers,
Sturla

[mschlesselman]

Hi @sturlaws

Thanks for taking a look at this. That is the output I am looking for, and works great. The only other nuance I have is that in my main data table, the count of items is not rolled up into a single row; there is an individual row for each item (to maintain unique properties). This would make it difficult to add a column to show new items from previous date I think. So adding a count or countrows and back as a measure may be better. Let me know if you have any other thoughts on this. I have attached a screenshot that may be a little more reflective of the data.

Thank you again for your help. 

[mschlesselman]

For anyone that may have the same issue - I was able to figure out a solution. I created a duplicate query of the dataset  containing all the unique data, but only kept the identifiers that I wanted to group by (Area,  Material etc.). I then grouped the data using a countrows and then applied the solution that @sturlaws provided. 

Thanks!