To calculate based on Percent of Total x Monthly Target

[thew]

As reference to post earlier Solved: Re: To calculate Percent of Average Results - Microsoft Fabric Community

Thanks i now able to create the Average % : 

AverageDailyWholesales(%) = DIVIDE(

[AverageDailyWholesales],

CALCULATE([AverageDailyWholesales], ALLSELECTED('Calendar'))

)

Now, how can I create another measure where to calculate the Daily Target where I have another table for monthly target

Simple formula but it can't show daily as expected: 

[AverageDailyWholesales(%)] * SUM('Monthly Target'[Value] 

Once I have these value then i need to create Cummulative Target. 

[v-hashadapu]

Hi @thew ,
I wanted to follow up and see if you’ve had a chance to review the information provided here.
If any of the responses helped solve your issue, please consider marking it "Accept as Solution" and giving it a 'Kudos' to help others easily find it.
Let me know if you have any further questions!

[thew]

Hi @v-hashadapu , Thanks for the solution, although it doesn't entirely fit but i will alter based on your provided sample. 

[thew]

Thanks for the reply, appreciate it. I tried both solution provided by @rajendraongole1  @v-hashadapu doesn't seems working. 

I tried with this formula which seems to be working:

Daily Target =

VAR CurrentDate = MAX('Calendar'[Date])

VAR TargetMonth = EOMONTH(CurrentDate, 1) 

VAR MonthlyTarget =

    CALCULATE(

        MAX('Monthly Target'[Value]),

        FILTER(

            ALL('Monthly Target'),

            EOMONTH('Monthly Target'[Date], 0) = TargetMonth

        )

    )

RETURN

[AverageDailyWholesales(%)] * MonthlyTarget

The idea is to take average wholesales for the past 3 months, and calc the average sales daily. 

Monthly target is future month +1 number which need to base on. 

Now I am still trying to get the CumulativeTarget. 

[v-hashadapu]

Hi @thew , Please consider below: 

CumulativeTarget =
VAR CurrentDate = MAX('Calendar'[Date])
VAR StartOfMonth = DATE(YEAR(CurrentDate), MONTH(CurrentDate), 1)
VAR CurrentYearMonth = YEAR(CurrentDate) * 100 + MONTH(CurrentDate)
VAR DaysToSum =
FILTER(
DATESBETWEEN('Calendar'[Date], StartOfMonth, CurrentDate),
YEAR('Calendar'[Date]) * 100 + MONTH('Calendar'[Date]) = CurrentYearMonth
)
VAR Result =
CALCULATE(
SUMX(
DaysToSum,
[Daily Target]
),
ALLSELECTED('Calendar'[Date])
)
RETURN
IF(ISBLANK(Result), 0, Result)

If this helped solve the issue, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.

[thew]

Hi @v-hashadapu , 

not sure where's goes wrong, it turns up like this:

[v-hashadapu · Accepted solution]

Hi @thew , Thank you for reaching out to the Microsoft Community Forum.

Please see the attached .pbix file for your reference.

If this helped solve the issue, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.

[v-hashadapu]

Hi @thew , Thank you for reaching out to the Microsoft Community Forum.

We find the answer shared by @rajendraongole1  is appropriate. If it doesn't work, please consider below:

You should set up a reliable way to link your Calendar and Monthly Target tables. Instead of relying on text-based formats like "MMM-yy", use a numeric key for each month. In the Calendar table, create a calculated column:

YearMonthKey = YEAR('Calendar'[Date]) * 100 + MONTH('Calendar'[Date])

In your Monthly Target table, assuming its Date column refers to the first day of each month, add the same type of column:

YearMonthKey = YEAR('Monthly Target'[Date]) * 100 + MONTH('Monthly Target'[Date])

This ensures consistent month level matching between the two tables, regardless of regional formatting differences or text inconsistencies.

Now, let’s define the Daily Target measure. This will take the monthly target, divide it evenly across all days in the month and then adjust each day's value based on its proportion of the total. Here’s the DAX:

DailyTarget =

VAR CurrentYearMonth = YEAR(MAX('Calendar'[Date])) * 100 + MONTH(MAX('Calendar'[Date]))

VAR MonthlyTarget = 

   CALCULATE(

       SUM('Monthly Target'[Value]),

       FILTER(

           'Monthly Target',

          (YEAR('Monthly Target'[Date]) * 100 + MONTH('Monthly Target'[Date])) = CurrentYearMonth

       )

   )

VAR DaysInMonth = DAY(EOMONTH(MAX('Calendar'[Date]), 0))

VAR BaseDailyTarget = DIVIDE(MonthlyTarget, DaysInMonth, 0)

RETURN

[AverageDailyWholesales(%)] * BaseDailyTarget

Next, we'll create the Cumulative Target measure, which adds up the daily targets from the beginning of the month up to the current day. This allows you to track progress toward the monthly goal on a day-by-day basis. Here's the DAX:

CumulativeTarget =

VAR CurrentDate = MAX('Calendar'[Date])

VAR StartOfMonth = DATE(YEAR(CurrentDate), MONTH(CurrentDate), 1)

RETURN

CALCULATE(

   SUMX(

       DATESBETWEEN(

          'Calendar'[Date],

          StartOfMonth,

          CurrentDate

       ),

       [DailyTarget]

   ),

  ALLSELECTED('Calendar'[Date])

)

 
We think this will do it. If it doesn't work or if you want more information. Please refer below links:

Power BI Distributing/Allocating the Monthly Targe... - Microsoft Fabric Community

Solved: Convert Monthly Target to Daily Target with a twis... - Microsoft Fabric Community

sql server - Calculate daily targets based on monthly targets Sales Power bi - Stack Overflow

[rajendraongole1]

Hi @thew  - you already created, AverageDailyWholesales(%).

now let's create another measure for daily target as below

DailyTarget =
VAR MonthlyTarget =
CALCULATE(
SUM('Monthly Target'[Value]),
TREATAS(VALUES('Calendar'[MonthYear]), 'Monthly Target'[Date])
)
RETURN
[AverageDailyWholesales(%)] * MonthlyTarget

If you're not using a MonthYear column yet, create one in both tables

Now let's create a cumulative targets as below with filter and all func.

CumulativeTarget =
CALCULATE(
[DailyTarget],
FILTER(
ALL('Calendar'),
'Calendar'[Date] <= MAX('Calendar'[Date])
)
)

try the above and let us know.