Dealing With Seasonal Budget Allocations in Power BI

YouTube / Blogs / Other
1

Sam’s video reminded me of an issue I have.

We have a daily revenue dashboard which allows the user to compare the daily revenue to a budget allocated by days in the month and the two year historical work day average.

Rarely, some charges will be entered incorrectly, generating an enormous revenue amount, say, 10 million. The following day, someone catches the error and will then create a reversing entry of -10 million. These entries create very large swings in the historical work day average.

The data entry issue is always going to be there. How can I mitigate these swings? What would be the best way to exclude outliers?

2

HI there

That issue bring back memores for me!!! We had a 10 m error when I was doing reporting on weekly basis. We made a correcting general Ledger entry for the day it happen. For example if the error happen on Monday May 8 but it wasn’t discovered until Monday May 15. We made the general ledger correcting error entry for May 8 not May 15. The net affect would be zero for that day.

If the error happen within a system generate then go back to the source and correct it there for the same date as the error happen. If the error couldn’t be corrected until the next month then make a entry for when the entry happen in May and reverse it out when the system is corrected in June.

If you know exactly what day it happen on then just disregard the 10 m and do the difference of it for budgeting purpose.

Both ways the net affect would be zero.

I hope this helps.

Thanks
Keith

3

Hello @kaDargo

Did the responses above help solve your query?

If not, can you let us know where you’re stuck and what additional assistance you need?

If it did, please mark the answer as the SOLUTION by clicking the three dots beside Reply and then tick the check box beside SOLUTION

Thank you

4

EDIT…it looks like I didn’t hit the Reply button .

Hi Keith, thanks for the reply.

This is historical data from a patient billing system. The information has already been corrected. Using your example, the business office entered a charge for $10m on May 8th and discovered the error on May 15th and created a reversal for ($10m).

I’m wondering if determining a Z score for the workday would be a solution?

5

I think i would just make a comment area that states the error information. (just abnormal activity)

If it happens again, I would make sure a general ledger entry is made for that day that it error happen. When the system gets corrected then reverse that entry on the day its corrected.

thanks
Keith

6

Since the errors are usually caught within a day or two of the error, the reversals are loaded to the GL at the same time with EOM as the date. It’s in the daily reporting from the billing system that I’m trying to address.

7

All I did in that case, I made a note comment on the report that it was being investigated. Personally, i wouldn’t do an EOM entry, I always did an entry on the day of the entry the error occured.

8

Hello @kaDargo

Good to see that you are having progress with your inquiry. Did the responses above help you solve your inquiry?

If it does, kindly mark his answer as the Solution to your query.

If not, how far did you get and what kind of help do you need further?

9

Hi @kaDargo

We’ve noticed that no response was received from you on the post above.

Just following up if you still require assistance to solve your inquiry.

In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

10

Hi @kaDargo

We noticed that your inquiry was left unsolved for quite some time now.
Looks like your inquiry was out of the experts’ and users’ bounds.

If you were sure you provided all pertinent context to your concerns and read how to use the forum more effectively and still find your question unanswered, your membership also comes with relevant resources that may help you with your Power BI education, so we advise that you check these resources as well.

While our users and experts do as much as reasonable to help you with your inquiries, not all concerns can be attended to especially if there are some learnings to be done. Thank you!

1 Like