Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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.
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!
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..
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.
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!
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |