Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rttplm
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

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
MFelix
Super User
Super User

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



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!

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



Hi @MFelix,

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

Many thanks for your help!

 


Untitled3.png

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.