cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bobbybamber Frequent Visitor
Frequent Visitor

Splitting Monthly Targets Into Daily Ones

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Splitting Monthly Targets Into Daily Ones

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

5 REPLIES 5
Super User
Super User

Re: Splitting Monthly Targets Into Daily Ones

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




bobbybamber Frequent Visitor
Frequent Visitor

Re: Splitting Monthly Targets Into Daily Ones

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?

Highlighted
Super User
Super User

Re: Splitting Monthly Targets Into Daily Ones

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

bobbybamber Frequent Visitor
Frequent Visitor

Re: Splitting Monthly Targets Into Daily Ones

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

NeilBarrett Regular Visitor
Regular Visitor

Re: Splitting Monthly Targets Into Daily Ones

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

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (2,208)