Forum Discussion

Luddekudde's avatar
Luddekudde
Advocate I
8 years ago
Solved

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 ...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    8 years ago

    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's avatar
    Luddekudde
    8 years ago

    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! :smileyhappy:

Floriankx's avatar
Floriankx
Solution Sage
8 years ago

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's avatar
Luddekudde
Advocate I
8 years ago

Floriankx

 

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: