Forum Discussion
Running Total per category
Hello,
I tried the steps outlined here but I'm still having trouble An example of my data is below.
What I'm trying to do is make a running total of the applications we get from students. Ideally I want to make a bargraph of the months, with a line of the "running total" to show how many applications we have received at each date of our choosing. I'm hoping this can be done through slicers and not have it impact the formulas
I've tried several calculations but the closest I was able to get is a running total for the entirety of the dataset, but it didn't separate it out by app term.
What we are looking to do is compare our application numbers as they come in so we can take action if they seem lower than the trends of previous years. Our terms start in September and end on April 30 so even our YTD is a bit off from the standard calendar year.
Any guidance is appreciated!
Hi justinbouchard ,
Do you want to calculate the running total per app_term and based on date?
Try this code.
Measure = CALCULATE(SUM ( Sheet1[app number] ),FILTER(ALL(Sheet1),Sheet1[APP_TERM] = MAX(Sheet1[APP_TERM])&&Sheet1[Date Received]<=MAX(Sheet1[Date Received])))Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 Replies
justinbouchard , If you need running total
with help from date table
Cumm = CALCULATE(Count(Table[Student ID]),filter(allselected('Date'),'Date'[date] <=max('Date'[date])))
YTD
Cumm = CALCULATE(Count(Table[Student ID]),datesytd('Date'[date], "8/31"))
If you need Half year YTD or any term YTD refer this
Half Year Time Intelligence, Half Year Till date: https://youtu.be/fkm3Xvjcpl0
Not working still. I just get this:
The last column isn't counting still. I followed your example for making a date table
Calendar = CALENDAR(date(2015,01,01), date(2023,12,31))and i have a one to many relationship with my application table.
Formula was
Cumm = CALCULATE(Count(Sheet1[Student ID]),filter(allselected('Calendar'),'Calendar'[Date] <=max('Calendar'[Date])))Or should I be using my date tables from sheet 1 in the filter?Hi justinbouchard ,
Do you want to calculate the running total per app_term and based on date?
Try this code.
Measure = CALCULATE(SUM ( Sheet1[app number] ),FILTER(ALL(Sheet1),Sheet1[APP_TERM] = MAX(Sheet1[APP_TERM])&&Sheet1[Date Received]<=MAX(Sheet1[Date Received])))Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
That did it! I tried to apply this to my environment with the real data and it worked! I had to tinker around with the relationship data and remove my date table but i'll tinker with it until I have it all working. Thank you so much!
