Forum Discussion
Column filtering
Without data, can't recreate but your measure should be something like:
Total Count = VAR maxDate = MAX(sys_created_on) RETURN SUMX(FILTER(ALL(Table),[sys_created_on]<maxDate),[Ticket Created])
Basically, get the max date in the current context (last ticket in the current month). Grab all of the entries and filter out so that you get everything less than that date and sum up the tickets.
You could also use COUNTROWS instead of SUMX as Floriankx suggested.
I understood why I got the wrong numbers, I had some essential filters on the report that got removed by the ALL function.
Thanks for the help! :smileyhappy:
I would create a measure for your rolling sum rather than a column. There are Quick Measures for this.
I tried with the quickmeasure but it doesn't work. I don't get the rolling total, I just get the periodic values like before.
What do you exactly mean by Rolling Sum?
Something like DATEISINPERIOD?
CALCULATE(SUM([Tickets Created],DATEISINPERIOD(dateTable[Dates],Max(SelectedDate),-12,months))
This would give you the sum for the last 12 months.
Didn't work unfortunatly :smileysad:
What I mean is that the bar for ex. June 2017 should show the amount of tickets created in June 2017 plus the tickets created before. So for every month the value should grow with the amount of tickets created that month, do you follow?
Instead of showing the amount of tickets created per month, as it does now, it should show the total amount of tickets created, up until that month, including the ones created that month, of course.
And thanks for the help, by the way :smileyhappy:
Without data, can't recreate but your measure should be something like:
Total Count = VAR maxDate = MAX(sys_created_on) RETURN SUMX(FILTER(ALL(Table),[sys_created_on]<maxDate),[Ticket Created])
Basically, get the max date in the current context (last ticket in the current month). Grab all of the entries and filter out so that you get everything less than that date and sum up the tickets.
You could also use COUNTROWS instead of SUMX as Floriankx suggested.
It look something like what I'm looking for, only the numbers aren't matching. The differences are twice as large as they should be, do you know what could cause this?
Thanks for all the help, it feels like we're close :smileyhappy:
