Forum Discussion

rttplm's avatar
rttplm
New Member
8 years ago
Solved

Rolling calculation

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!

 

  • rttplm's avatar
    rttplm
    8 years ago

    Hi MFelix,

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

    Many thanks for your help!

     


5 Replies

  • MFelix's avatar
    MFelix
    Super User
    8 years ago

    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's avatar
      rttplm
      New Member
      8 years ago

      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's avatar
        MFelix
        Super User
        8 years ago
        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