Forum Discussion
Column filtering
Hello, I have a problem with filtering columns. In my report I would like to count the number of tickets created based on the month and year to later create a rolling sum. What I have tried to do is to categorize all the dates into a month and date column, like this:
[sys_created_on] are the original dates from the source-file. Month picks out the month and year in a date format.
What I tried to do is create another table (find below), with the same months and years as the [Month] column and then count the number of times the date-value appeared in the first table. I tried sorting the dates using the [Month] column, shown below, like this:
Tickets Created = CALCULATE(COUNT(
'SNOW(sn_sm_finance_request)'[Tickets created]),
'SNOW(sn_sm_finance_request)'[sys_created_on] = 'Dynamic Filter'[Month])
But it just tells me you can't have two columns in a true/false filter.
Any suggestions on how I could do this another way?
Thanks!
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:
9 Replies
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.
Hello,
Why don't you just create a sum. If you create a Pivot and add Month to rows it should give you the expected result.
Are the month columsn related?
Hello Floriankx
Maybe it wasn't clear in my post, to get the amount of tickets I need to count the amount of rows, because one row = one ticket.
I don't think SUM is the function to use here..
I have tried adding a relationship between the month columns but that doesn't help me, unfortunatly..
