Divide a Single Number Between Days in a Period

[JDiOrio]

Hello everyone, and thanks in advance!

Summary:
I am trying to take a Budget that is built around a single Budgeted Amount per Salesperson per Month and through a formula divide that up over the Days within each Fiscal Month to be able to compare to Daily Actual Sales.

Tables:

• DIVISIONBUDGET - Sales are Budgeted for each Sales Person for each Month.

• DIVISIONMAP - SLSM# field is used to tie the Budget to Actual Sales tables.

• FiscalDateTable2 - we are on a Fiscal Calendar here. Therefor the Fiscal Date Table is used to properly tie Actuals and Budget into the proper Accounting period.

• order_history1 - Actual Sales table. These update daily.

Background:
Actual & Budget Sales tables are tied to the Division Map based on SLSM#. Actual Sales are tied to the Fiscal Date Table based on Invoice Date, for which there are transactions every day. The Budget however has all Budgeted Sales for each Sales Person shown on a single day, which is the last fiscal day of any given fiscal period.

So this creates a couple of issues. First, we can’t really perform any meaningful Daily metrics in terms of Actual to Budget comparisons, because there is no Budgeted amount except for on that last day of each fiscal period. Second, the earlier in the month it is, the Delta between Actual and Budget is simply huge, and then gradually decreases as the month goes on. But that is kind of confusing and not always how our team would like to view the information. Hopefully the screenshot illustrates what I’m talking about.

Question:
Is there a way to take the Budget and divide it up equally between the days within a fiscal period at the SLSM# level? I’m really hoping that there’s a way to do this via formula.
Daily Sales Budget Support Request (eDNA).pbix (631.9 KB)
PBIX attached

[BrianJ]

@JDiOrio,

First, thanks very much for the clear explanation of the problem and for providing a PBIX file - that makes it much easier for us to provide good support.

Quite a challenging problem, due to the granularity mismatch between your sales and budget tables, as well as the end of period not corresponding to the end of month. Please check my numbers to be sure I properly captured your requirement, but I think this solution works:

The first step was to remove the active relationship between your date table and the budget table. Because the endpoints of the relationship are both dates, PBI will let you create the relationship, but because of the aforementioned granularity mismatch, filtering using that relationship will not produce the proper results.

The second step was to create a Days in Period measure, which is the number of days from the end of the prior month’s closing date to the current month’s closing date. Here’s that measure:

Days in Period = 

VAR CurrentEnd =
    SELECTEDVALUE( FiscalDateTable2[End of Fiscal Per] )
VAR SelPersonNum =
    SELECTEDVALUE( 'DIVISIONMAP'[SLSM#] )
VAR PrevEnd =
    CALCULATE(
        MAX( 'DIVISIONBUDGET'[PeriodEndDate] ),
        FILTER(
            ALL( 'DIVISIONBUDGET' ),
            'DIVISIONBUDGET'[SLSM#] = SelPersonNum
                && 'DIVISIONBUDGET'[PeriodEndDate] < CurrentEnd
        )
    )
VAR Result =
    DATEDIFF( PrevEnd, CurrentEnd, DAY )
RETURN
    Result

Next, created the following measure for Budget Revenue, using lookups and virtual relationships via TREATAS to replace the active physical relationship between the date table and the budget table:

Budget Revenue = 

VAR SelDate =
    SELECTEDVALUE( FiscalDateTable2[Date] )
VAR SelMo =
    MONTH( SelDate )
VAR SelYr =
    YEAR( SelDate )
VAR LookupEndDate =
    CALCULATE(
        MAX( DIVISIONBUDGET[PeriodEndDate] ),
        MONTH( DIVISIONBUDGET[PeriodEndDate] ) = SelMo
            && YEAR( DIVISIONBUDGET[PeriodEndDate] ) = SelYr
    )
VAR Result =
    CALCULATE(
        SUM( DIVISIONBUDGET[Sales Amount] ),
        FILTER( DIVISIONBUDGET, DIVISIONBUDGET[PeriodEndDate] = LookupEndDate )
    )
VAR TotResult =
    CALCULATE(
        SUM( DIVISIONBUDGET[Sales Amount] ),
        TREATAS( VALUES( FiscalDateTable2[Date] ), DIVISIONBUDGET[PeriodEndDate] )
    )
RETURN
    IF( HASONEVALUE( FiscalDateTable2[Date] ), Result, TotResult )

Next, created the following measure to divide monthly Budget Revenue by the prior Days in Period measure to create a budget amount with daily granularity that then can be directly compared with the daily sales figures:

Bud Rev Alloc = 

CALCULATE(
    IF(
        HASONEVALUE( FiscalDateTable2[Date] ),
        DIVIDE( [Budget Revenue], [Days in Period] ),
        [Budget Revenue]
    )
)

Finally, created a (whew!) simple measure to subtract daily budgeted from actuals:

Actual to Budget Revenue = 
[TotalActualSales] - [Bud Rev Alloc]

I hope this is helpful. Full solution file attached.

• Brian
  eDNA Forum - Daily Sales Budget Support Request Solution.pbix (630.6 KB)

[EnterpriseDNA]

Hi @JDiOrio , did the response provided by @BrianJ help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

[JDiOrio]

Thanks so much @BrianJ! I will look to implement shortly (hopefully today??) and provide feedback. I’m excited to see the solution in action and so grateful for the help!

[BrianJ]

@JDiOrio,

Glad to help. This was an interesting one - the general pattern is pretty common, but I’d never run into this particular variation where the monthly start and end dates cross months.

Will look forward to hearing how your testing goes.

• Brian

[JDiOrio]

Hi @BrianJ! So I ran into a problem with the proposed solution…screenshot shown below. Because of the way Fiscal Periods end up, there are going to be some (there are 3 in 2021 for example) for which there is no Period End Date within a specific month. So for example, for March there is no Period End Date within that month…February ended on February 26th while March ended on April 1st. June and September will be the same way. So for those months there’s no Budgeted Revenue showing at all.

Now, I left it out of the Mock File, but both the Fiscal Date Table and the Budget have a Year-Month field (i.e. 21-03, 21-06, etc), so I’m not sure if something like that would help? I can try and mock up a new test file if necessary to show what I mean

[JDiOrio]

HI @BrianJ …I added the ‘Period’ column back into the Budget table, which has a similar column in the FiscalDateTable2 called ‘Year-Month’, if that helps at all?

eDNA Forum - Daily Sales Budget Support Request Solution.pbix (632.3 KB)

[BrianJ]

@JDiOrio,

OK, that’s an interesting wrinkle that I didn’t catch on the initial solution development. Shouldn’t be a big problem- we’ll just need to add in another logic check that if there’s not an end period date in a given month, we just allocate across all dates in that month.

Other than that, did the solution check out OK?

• Brian

[JDiOrio]

Yep, I think other than that it did @BrianJ!

[BrianJ]

@JDiOrio,

Cool. I’ll build out that additional logic later today and shoot you a revised solution.

• Brian

[BrianJ]

@JDiOrio,

Okay, working on building out the additional logic and need your guidance on how to handle the business rule for allocation down to daily level when a month has no fiscal pe.eEnd date. I can see two potential ways to handle this:

1. Create a placeholder fiscal. end date equal to the last day of the month. I did that here: for convenience as a calculated column, but if I were implementing this alternative for real, I would do it in Power Query.

2. Leave the fiscal per blank, and just allocate the values in the months missing a fiscal per date in the next month, over a combined two months period.

For example, using the summary table below, we can allocate March using the periods 2/26/21 to 3/31/21 and then from 4/1/21 to 4/30/21.(optiom 1). Or alternatively, from 2/26/21 to 4/30/21, leaving March blank and allocating all the March values into the April calculations (option 2).

Please let me know which option you prefer, or whether there are additional alternatives that I haven’t accounted for. Thanks.

– Brian

[deltaselect]

Hi @JDiOrio,

Interesting issue.
In addition to Brian’s solution, I have an slightly alternative solution, see attached PBIX, with a budget breakdown into days with two variants: all days of the month, and working days, excluding the weekends, as no sales is generated in the weekends.

In order to come to this solution, 3 steps are taken:

1. I took the freedom to add a column PeriodEndDate in the FiscalDateTable
   Also an index is added for eventual sorting.
   With the additional column Period End Date, you can compare Actual with Budget for every date, otherwise the budget figures are only seen on the Period End Date.

2. Break the one to many relationship FiscalDateTable with DivisionBudget, and calculate the Budget figures with a TreatAs function.

3. Measures are created: for Budget (workdays) and Budget (all days) and difference measures Actual to BudgetWD and Actual to BudgetAD .
   The budget is calculated per day or per workday by counting the days of Fiscal Months, from the Fiscal Date Table.
   NB: All calculations are based upon the provided Fiscal Date Table with dates, translated into Fiscal Months and fiscal days per Fiscal Month. (doing so there is no need for a “normal” Date Table with “normal” months and days in the “normal” months)

Daily Sales Budget Support Request (eDNA) DS v3b.pbix (333.3 KB)

It seems to work, including the totals.

If you have questions, or notice incorrectness, please let me know.
Kind regards,
deltaselect

PS 1:
The adjustments in the Fiscal Date Table were made in Excel, and to resolve an error in PowerQuery with “Year-Month” after uploading from Excel, a P was placed upfront, to make it text.

PS 2:
I tried to do this exercitation with Dax formulas; but this does not seem to work. A one to many relationship from FiscalDateTable to DivisonBudgut is required for creating the Comb.TablesBudget, and as a consequence completely wrong actual numbers are presented in the visuals.
" Comb.TablesBudget = SUMMARIZE( DIVISIONBUDGET, DIVISIONBUDGET[PeriodEndDate],
FiscalDateTable2[Year-Month],“MonthlyBudget”,[Budget Revenue]) "
" Period End Date = LOOKUPVALUE(‘Comb.TablesBudget’[PeriodEndDate],
‘Comb.TablesBudget’[Year-Month], FiscalDateTable2[Year-Month]) "

[JDiOrio]

@BrianJ,

After thinking it through at length, I believe Option 1 would yield the most well understood results and at least in my mind seems to be the clearest path

[JDiOrio]

@deltaselect - Thanks so much! I’ll take a look at this as well! I love too many solutions more than not enough!

[BrianJ]

@JDiOrio,

I agree that’s a more intuitive way to handle the missing end periods, but wanted to confirm before investing time in a solution. Will send you a revised version of my solution this evening.

@deltaselect - nice work! I think the threads with multiple solution approaches provided are always the most interesting.

• Brian

[BrianJ]

@JDiOrio,

I’m still wrestling with this one. Adapting the End of Fiscal Period code to take account of our revised business rule above is very straightforward:

 End of Fiscal Per = 

VAR PrimaryEndFP =
CALCULATE(
    MAX( DIVISIONBUDGET[PeriodEndDate] ),
    FILTER(
        DIVISIONBUDGET,
    MONTH( DIVISIONBUDGET[PeriodEndDate] ) = FiscalDateTable2[MonthNo]  &&
    YEAR( DIVISIONBUDGET[PeriodEndDate]) =  FiscalDateTable2[Year]
))

VAR AltEndFP =
EOMONTH( DATE( FiscalDateTable2[Year], FiscalDateTable2[MonthNo], 1 ), 0 )

VAR Result =
COALESCE( PrimaryEndFP, AltEndFP )

RETURN Result

So far, so good. But now we have another problem with our Division Budget table:

We are missing some months, but that doesn’t bother me since I expect we will apply the same business rule as above and use the last day of the month. However the thing I don’t know how to handle is some months have multiple end dates… In that case, which date should we use?

Also, shouldn’t the budget period end date for each month correspond to the end date for that month in the fact table?

Thanks in advance for the clarification.

• Brian

[EnterpriseDNA]

Hi @JDiOrio, did the response provided by the users and experts help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

[JDiOrio]

Hi @BrianJ! I’m not sure I understand about the dates not matching. As an example, I’m seeing that the last Date within the FiscalDateTable2 for Period 21-03 for example is 04/01/2021, while in the DIVISIONBUDGET table the PeriodEndDate for all entries within Period 21-03 is also 04/01/2021. Apologies in advance…I’m probably not looking correctly at what you’re calling out (??).

But to you’re initial question regarding which date to use when multiple are present, that’s a great question. I need to think on that one.

Let me ask this, which I know is not really elegant but it honestly didn’t originally occur to me until just now. The FiscalDateTable is going to need to be manually maintained each year anyway. Would it be a lot easier if I simply manually added a column both for ‘End of Fiscal Period’ as well as ‘# Days in Period’ in the FiscalDateTable itself? So, similar to how each line for Period 21-01 shows 1/29/2021, each would show 28.

If this makes things immensely easier, again my apologies for not thinking of it sooner. I’ve never worked for a company that didn’t operate on a Calendar month, so getting past these time measure hurdles are new to me.

[EnterpriseDNA]

Hi all! Bumping this post again for more visibility.

[BrianJ]

@JDiOrio ,

Apologies that my earlier message wasn’t clear. Here’s the crux of the issue, I think:

Obviously, for actual vs. budget variance calculations to be meaningful, they need to be compared with periods of identical length. That’s currently not the case for the periods indicated by the red arrows. These need to be aligned, whether through DAX, PQ or direct entry into the FiscalDateTable.

Until that point, I think we’re stuck…

Thoughts?

• Brian