Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
VeemalS
Helper I
Helper I

Target Allocation by Day

Hello,

 

I have a Target Table in EUR as follows

MonthTarget
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
4 REPLIES 4
VeemalS
Helper I
Helper I

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

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:

RicoZhou_0-1664351324388.png

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.

RicoZhou_1-1664351358602.png

 

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.

 

Unfortunately this has not resolved the issue that I am having

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

 

Brgds

 

Veemal

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.