Splitting Monthly Targets Into Daily Ones

[bobbybamber]

Hi guys,

Run into a bit of a problem, and not sure if there's a more sophisticated way of going about it than I'm currently planning.

Currently we've got a monthly target, split by product and by channel. Expressing this as a monthly target vs result is fine, but I'm wondering what the best way of expanding it into a daily target is.

The data, as an example, would look like this:

MONTH - CHANNEL - PRODUCT - SPEND
November - Online - Prod A - £500
November - Offline - Prod A - £300
November - Online - Prod B - £400
November - Offline - Prod B - £200
December - Online - Prod A - £500
December - Offline - Prod A - £400
December - Online - Prod B- £400
December - Offline - Prod B - £200

-

Now, mapping this against monthly performance is fine. Question I have is this, I can get the performance/result data in daily data - is there a way of "dividing" the spend data across the month (to produce a daily average), but still being able to input it as a monthly figure?

Currently when I'm matching it, the monthly part is fine, but I can't drill down any further.

Thanks,

Bob

[MFelix]

HI @bobbybamber,

Taking into account your needs I would add a column on your table with the daily target just by dividing number of days buy the spend amount.

Only question is are you having daily sales including weekends or just working days?

Regards,

MFelix

[bobbybamber]

Hi @MFelix

That is, essentially, what I'm trying to avoid. The example looks quite simple but we've got about a dozen different products and three different channels. 12x3x31= about 1,000 rows per month to be uploaded. Just wondered if there was a way of Power BI dealing with the daily part itself?

[MFelix · Accepted solution]

Hi @bobbybamber,

You don't need to upload you just need to add a calculated column to your model with the division

Something like:

Daily Target =
DIVIDE (
    Table[Spend],
    SWITCH ( TRUE (), Table[Month] = "January", 31, Table[Month] = "February", 28 , ...)
)

Be aware that in your table you have Months in Text so I have made the Switch function (... to be replace by all other values) but if you add the end of month date in your table you simply have to write:

Daily Target =
DIVIDE (
    Table[Spend],
    DAY(Table [End_of_Month])
)

Regards,

MFelix

[Anonymous]

How does one extend this solution to deal with workdays?

I have a Budget Table (Columns of Date, Region, Qty, Sales$) and a related date table (Date, Year, MonthNumber, etc).

Sales team want to see their daily sales line (Easy) charted with their budgetted sales line. But the budget data doesnt exist on a daily basis, just a monthly one.

I thought i'd calculate a denominator of

NetWorkDays = COUNTROWS(FILTER(ALL(Dates), Dates[WorkDay] = 1 && Dates[Month Number]=Dates[Date].[MonthNo] && Dates[Short Year]=Dates[Date].[Year]))

i.e. throw off the current context filter and refilter to rows that match the year and month and have workday = 1 (i.e. true). But this doesn't work

[JoeDoe]

Hi @Anonymous  I wonder if your problem was solved? I am facing the exactly same problem here. Any solution?

Thanks,

[bobbybamber]

Excellent, @MFelix, thank you. Will have a dart at this today.