Calculate sum on any given day for data identified by range

[StephenCudd]

Hello Power BI experts, I am new here although I have been using Power BI for a few years. I am struggling with what seems a relatively simple problem but is driving me nuts. Hopefully somebody can suggest a solution.

I have a fact table of service data and a date dimension. For this example I have eliminated the join between the tables but previously they were joined on planned service date. So no problem summarising planned service hours by day. My objective in this case is to determine, for any day in the past, how many planned service hours were overdue at that moment in time. On the fact table I have created two calculated columns that determine the date at which the planned service became overdue and the date at which it was completed. My summary therefore, is the number of planned service hours on any given day for which the "start of overdue date" has passed and the "end of overdue date" hasn't been reached.

I created a measure on my dates dimension as follows:

Overdue tracker = calculate(sum(service_hours), filter(fact_table, start_of_overdue_date <= selectedvalue(measuredate) && end_of_overdue_date >= selectedvalue(measuredate))))

It works to some degree however it takes a very long time to recalculate (up to 10 minutes) which suggests it is very inefficent and makes it difficult to assess accuracy. I want to retain the option to filter and slice the output so a calculated column or table doesn't really answer the problem.

Any suggestions would be greatly appreciated.

[Anonymous]

Hi @StephenCudd ,

Your measure looks fine and not complicate at all. What data source are you using and how many data you have?

And if it's about calculation, better to show some sample data to us.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[StephenCudd]

Hi @Anonymous , thanks for responding. The data I am using, in simple terms, is:

Fact Table (2.5 million rows)

RecordNo - 1,2,3,4,5,etc

Start_Of_Overdue_Date - 01/01/2000, etc

End_of_overdue_date - 31/12/2000 etc

Service_hours - 5.1, 3.2, 1.5, etc

Date Dimension (18,250 rows, each date from 01/01/2000 to 31/12/2050)

MeasureDate

One thing that has become apparent is that measures seem to be evaluated for the whole dataset each time rather than just for what is being visualised. I have partly solved the problem by wrapping the calc in an if statement to limit the calculation just to the last year.

Overdue tracker = if(selectedvalue(measuredate) <= today() && selectedvalue(measuredate) >= (today() - 365), calculate(sum(service_hours), filter(fact_table, start_of_overdue_date <= selectedvalue(measuredate) && end_of_overdue_date >= selectedvalue(measuredate)))), 0)

This has made visualisation performance acceptable however I was previously under the impression that measures are only evaluated for the filtered and sliced data being represented. In other words, without the IF statement, if I filter to just show data for the last year the measure will still calculate for all 18,250 rows on my date table. Is this correct?