Allocate Budget by Date and Type

[dkalina97]

I have watched YouTube videos and checked topics here but I have been struggling with my budget allocation all week and this is the last item I need to finalize my model.

I have linked my Budget Table to my Sales by Type table. I am attempting to use the Budget Allocation measure below per a tutorial, however, because that product type only had sales on 9 days, it is allocating it over 9 days and thus $7,197 per day, not over the number of days in June, which would make the allocation $2,159.10 per day. Issue 2, because there have not been sales by this product type, the 20,108 in MTD sales, should be compared to $41,022.90 (19 days MTD).

Budget Allocation 1 =

VAR DaysinContext = CALCULATE(COUNTROWS(Dates),all(Dates),values(Dates[Date]))

VAR DaysinMonth = CALCULATE(COUNTROWS(Dates), ALL(Dates), VALUES(Dates[YearMonthnumber]))

VAR CurrentMonth = SELECTEDVALUE(Dates[Month Name])

VAR MonthlyBudgetAmount =

CALCULATE([Total Budget Sales],TREATAS(VALUES(Dates[Month Name]), Budget[Month Name]))

Return

If( OR( HASONEVALUE(Dates[Date]),HASONEVALUE(Dates[YearMonthnumber])),

DIVIDE(DaysinContext, DaysinMonth,0) * MonthlyBudgetAmount,

[Total Budget Sales])

[dkalina97]

This will not work as there is not an association between the budget table and the dates table. The budget is linked to the sales type.

[amitchandak]

@dkalina97 , Have you tried something like

Power BI Distributing/Allocating the Monthly Target(Convert to Daily Target): Measure ( Daily/MTD): https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Monthly-Target-...

Make sure date table has all the dates