Column filtering

[Luddekudde]

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!

[Greg_Deckler · Accepted solution]

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.

[Floriankx]

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?

[Luddekudde]

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..

[Greg_Deckler]

I would create a measure for your rolling sum rather than a column. There are Quick Measures for this.

[Luddekudde]

@Greg_Deckler

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.

[Greg_Deckler · Accepted solution]

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.

[Luddekudde · Accepted solution]

@Greg_Deckler

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!

[Luddekudde]

@Greg_Deckler

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

[Floriankx]

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.

[Luddekudde]

@Floriankx

Didn't work unfortunatly

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