Target Allocation by Day

[VeemalS]

Hello,

I have a Target Table in EUR as follows

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

My objective is to allocate the target by Day.

I have been able to get the allocatedDayTarget by dividing the TargetAmount per mth by the no days in each Month.

TargetAllocationAmount = Divide(sum(Target[Target]),

                    Calculate(sum(Target[Target]),Treatas(Distinct('TIME'[MonthName]),Target[Month]) ))

FYI, I have a Time Dimension with a Datekey and metadata as MonthName &  Year

I will need to compute the MTD Amount of the daily Target Allocation to be able to compare with SalesAmount MTD.

I am not able to do so because I have no relationship between Target Table and the Time Table so

TargetMTDAmount = Calculate(TargetAllocationAmount, DatesMTD('Time'[DatesKey]) does not work.

Please help

Veemal

[VeemalS]

Hello,

Just to clarity that the wrong dax code was posted yesterday.

In fact to calculate the no of days in the month I have used the following:

DayInMonth =

var dat = min(Dates[DatesKey])

return

CALCULATE(DATEDIFF(DATE(YEAR(dat), MONTH(dat),1), EOMONTH(dat,0),DAY)+1)

Then we have created a another measure Target Day allocated by dividing the Target Monthly amount (EUR) by the Days in the month

thanks

Veemal

[Anonymous]

Hi @VeemalS ,

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.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[VeemalS]

Unfortunately this has not resolved the issue that I am having

[VeemalS]

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

Brgds

Veemal