Rolling calculation

[rttplm]

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!

[MFelix]

Hi @rttplm,

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 )

Regards,

MFelix

[rttplm]

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.

Many thanks!

[MFelix]

Hi @rttplm,

Makes this change to.your.measure

Measure_ =
VAR date_selector =
MAX ( Tickets[Report Date] )
RETURN
CALCULATE ( SUM ( Tickets[Indicator] ),ALL(Tickets),Tickets[Report Date] <= date_selector )

However depending on the size of.your table you may get some performance issues, please check if its works fine if not please tell me si i can change it. Not in the computer righth now to test another formula.

Regards,
MFelix

[rttplm · Accepted solution]

Hi @MFelix,

I tried the update measure, it works like a charme.

Many thanks for your help!

[MFelix]

Hi @rttplm,

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.

Regards,

Mfelix