Target Allocation by Day

Question

Hello,&nbsp;I have a Target Table in EUR as followsMonthTargetApr&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 20,965,000May&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 18,876,560Jun&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 17,987,908Jul&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 16,897,789Aug&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 25,987,654Sep&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 23,765,456Oct&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 18,907,654Nov&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 24,908,765Dec&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 23,876,543Jan&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 25,678,654Feb&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 19,876,546Mar&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 27,890,989&nbsp;My objective is to allocate the target by Day.&nbsp;I have been able to get the allocatedDayTarget by dividing the TargetAmount per mth by the no days in each Month.&nbsp;TargetAllocationAmount = Divide(sum(Target[Target]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Calculate(sum(Target[Target]),Treatas(Distinct('TIME'[MonthName]),Target[Month]) ))&nbsp;FYI, I have a Time Dimension with a Datekey and metadata as MonthName &amp;&nbsp; Year&nbsp;I will need to compute the MTD Amount of the daily Target Allocation to be able to compare with SalesAmount MTD.&nbsp;I am not able to do so because I have no relationship between Target Table and the Time Table so&nbsp;TargetMTDAmount = Calculate(TargetAllocationAmount, DatesMTD('Time'[DatesKey]) does not work.&nbsp;Please help&nbsp;Veemal

veemals · Answer

Hello,&nbsp;Just to clarity that the wrong dax code was posted yesterday.&nbsp;In fact to calculate the no of days in the month I have used the following:&nbsp;DayInMonth = var dat = min(Dates[DatesKey])returnCALCULATE(DATEDIFF(DATE(YEAR(dat), MONTH(dat),1), EOMONTH(dat,0),DAY)+1)&nbsp;Then we have created a another measure Target Day allocated by dividing the Target Monthly amount (EUR) by the Days in the month&nbsp;thanks&nbsp;Veemal

anonymous · Answer

Hi&nbsp;VeemalS&nbsp;,
&nbsp;
My Date table is as below.
Date = 
ADDCOLUMNS( CALENDAR(DATE(2022,01,01),DATE(2022,12,31)),"Year",YEAR([Date]),"Month",MONTH([Date]),"MonthName",FORMAT([Date],"MMM"))
Relationship:

Measure:
Target by Day = 
VAR _COUNT =
    COUNT ( 'Date'[Date] )
VAR _TARGET =
    CALCULATE (
        SUM ( 'Table'[Target] ),
        FILTER ( 'Table', 'Table'[Month] = MAX ( 'Date'[MonthName] ) )
    )
RETURN
    DIVIDE ( _TARGET, _COUNT )
Result is as below.

&nbsp;
Best Regards,Rico Zhou
&nbsp;
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
&nbsp;

veemals · Answer

Thanks. The allocated amount by day is ok. However, from this how do I calculate an MTD amount?&nbsp;Brgds&nbsp;Veemal

veemals · Answer

Unfortunately this has not resolved the issue that I am having

Forum Discussion

Target Allocation by Day

4 Replies

Related content

Cost allocation

Allocate monthly target to day

Calculating Days late with specific target times

Invesment Allocation Vs. Target Matrix

Calculate Working Day Target from Annual Target

Month	Target
Apr	20,965,000
May	18,876,560
Jun	17,987,908
Jul	16,897,789
Aug	25,987,654
Sep	23,765,456
Oct	18,907,654
Nov	24,908,765
Dec	23,876,543
Jan	25,678,654
Feb	19,876,546
Mar	27,890,989