Hey, I´m trying to port reports from Crystal Reports and can´t get one visual created. I´m pretty sure its not that complicated but no way so far.
Its about reporting for a ticketing system, we want to show how many tickets were actually open at a specific point in time.
(Some tickets get closed the same day, others are closed month or even years later, so its a moving target.)
The tabel has the "ReportDate". Its the "Ticket opened Date" when the status is "Open" and ReportDate is the "ticket closed date" when the status is "Closed". "Indicator" is "+1" for open and "-1" for closed. So it should be just the SUM of the "Indicator" over "ReportDate".
But what I get is the total per year. Its 3169 for Y2012 and -171 for Y2013 rather than 2998 for Y2013.
When we drill down into the years and months, we´d like to see how many tickets were open on i.e. 03/07/2016.
Any help would be highly appreciated!
Solved! Go to Solution.
You need to filter the values by date use something like this:
Measure_ = VAR date_selector = MAX ( Tickets[Report Date] ) RETURN CALCULATE ( SUM ( Tickets[Indicator] ), Tickets[Report Date] <= date_selector )
Hi, many thanks, I tried it.
The Measure over ReportDate gives me still the same number, i.e. -171 for Y2013.
It should not reset the calcuation at year end, or drilled down into the month at the end of the month.
Not sure how to achive this.
Good to know it works, but please check if you don't get any performance issues, it's not a good practice to make a ALL filter to a table we should do it to specific fields,
If needed I can check to improve that formula if your table is not very big you can leave as is.
We're excited to announce our first cross-community 'Can You Solve These?' challenge!
Visit our Community Blog for articles, guides, and information created by fellow community members.
Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.
Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.