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 ...
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

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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's avatar
JoeDoe
Frequent Visitor
6 years ago

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

 

Thanks,