I made a PowerBI report for a client, and they requested a revenue projection. Sometimes, business problems require simple solutions. You don’t always have to take the most complicated approach to solve business problems. A more complex solution would have been opening my jupyter notebook to do a model.fit, and model.predict
The client’s data source is live data. Hence, the projections needed to be recurrent, i.e. today is the month of October, a projection is calculated, and as the days proceed, the current revenue is put into factor when recalculating monthly projections. As new data is added, projections are recalculated.
How, then, do we achieve this?
Deploy DAX. That’s where the magic lies.
Six critical things needed include
Calendar Table
Public Holidays Table
Working to days to date exclusive of public holidays, i.e. how many days are considered business days from the beginning of every month to the current date, excluding days which are public holidays?
Total Working Days, i.e. how many days are considered business days from the beginning of every month to the end of that month, excluding days which are public holidays?
Revenue
Budget — Yearly budget was provided
Let’s write the DAX in PowerBI
Data Model
The Model used looks somewhat like this
The calendar table was created using the CALENDARAUTO() function, and i added other columns.
Similarly, the public holiday table was provided by the client.
Total Working Days
Let’s get our total working days using the following DAX.
The first part of the DAX measure, the variable calendar_working_days, counts the number of days between Mon-Friday on the calendar table, excluding weekends and including public holidays that fall on weekdays.
In the second part of the DAX measure, the variable public_holiday_working_days counts the days between Mon-Friday on the public holiday table, excluding weekends.
The RETURN function returns the working days exclusive of public holidays.
Working Days to Date
The DAX here works similar to Total Working days, the only difference being that the calendar table has been filtered not to exceed the current day.
You can view the output of both measures for 2022 below.
Today is a Saturday; hence isn’t considered a working day, so you have zero. October has 21 working days, but the 3rd of Oct. is a public holiday on the public holiday table, so you have 20.
Revenue Projections
The following conditions were applied when calculating the revenue projection.
If the month exceeds the current month, budget = projection, i.e. as of today, the current month is October. For Nov and Dec, Budget = projection.
If month ≤ current month, use the following formula
Daily Average = Total Revenue/Working Days to Date
For example, today is the 1st of Oct. Imagine it is a working day and revenue of $5000 was generated. The daily average will be 5000/1 = $5000
Actual Projected = Daily Average * Total Working Days
In Nigeria, October has two public holidays on Monday. Hence, the Total working days will be 19. From the example above, the actual projected will be 5000*19 = $19,000, i.e., at the end of October, the expected revenue should be around $19,000. Although as the days proceed, this projected revenue could fall or rise as it depends on how much is generated.
Below is the resulting DAX measure.
Plot Twist
The client returned and said, Abdullah, the current day needed only to be included as a business day at the close of work, i.e. if Monday is not a public holiday, it should be added as a business day only at the close of work.
The DAX below has been adjusted to meet that requirements
The total working day’s measure remains the same as the projection measure.
Based on the readjusted DAX, the working days to date for October will remain zero until 4 pm on Tuesday since Monday is a public holiday and business days are only counted for that particular day at the close of work which is 4 pm in this case.
That, my friend, is how I tackled a real-life situation dealing with a revenue projection.
Commentaires