cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

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".
Untitled.png
 

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!

 

Untitled2.png

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
New Member

Re: Rolling calculation

Hi @MFelix,

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

Many thanks for your help!

 


Untitled3.png

View solution in original post

5 REPLIES 5
Super User III
Super User III

Re: Rolling calculation

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





Highlighted
New Member

Re: Rolling calculation

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!

Highlighted
Super User III
Super User III

Re: Rolling calculation

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

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





Highlighted
New Member

Re: Rolling calculation

Hi @MFelix,

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

Many thanks for your help!

 


Untitled3.png

View solution in original post

Highlighted
Super User III
Super User III

Re: Rolling calculation

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors