Forum Discussion

bobbybamber's avatar
bobbybamber
Frequent Visitor
8 years ago
Solved

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

Need Help
  • MFelix's avatar
    MFelix
    8 years ago

    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

6 Replies

  • MFelix's avatar
    MFelix
    Super User
    8 years ago

    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's avatar
      bobbybamber
      Frequent Visitor
      8 years ago

      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's avatar
        MFelix
        Super User
        8 years ago

        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