cancel
Showing results for
Did you mean:
Helper II

## Urgent!! Sum of Sales Goal for current month ONLY

I have a "Sales Goal" table with "Sales Rep", "Attribute" (Month), and "Value"(Sales Goal Amount).

Column in Date Table:

``MonthDate = startofmonth('Date'[Date])``

Measures:

Gets the selected date based on my slider: EX: if I have selected 1/1/2021 - 10/15/2021 it will select 10/15/2021 as my selected date.

``Selected Date = MAX('Date'[Date])``

Gets the total amount of days per the selected month. EX: if i select 10/15/2021 it will output 31.

``````DaysInMonth =
DAY(EOMONTH('Date'[Selected Date],0))``````

Below is my current calculation to get the current selected months daily sales goal amount.

The goal is to get the daily amount (Sales Goal Amount / # of days in the month)

``````DailySalesGoalAMT = CALCULATE(
sum('Monthly Sales Goals 2021'[Sales Goal Amount])/[DaysInMonth],
FILTER(
all('date'),
'Date'[MonthDate]=DATE(Year([Selected Date]),Month([Selected Date]),Day([Selected Date])))
)``````

The issue I am having with this is that the output is summing all the daily amounts for all 12 months of the year.

For arguments sake, lets say the daily amount for each month is 5000. It is summing the 5000 per month by the 12 months totalling 60,000. I want it to output just the 5,000 amount for the current month I have selected based on the slider.

Another Example:

Lets say Nick has a monthly sales goal amount for October of 100,000 and November of 150,000. If i select any day within october I want it to output (100,000/# of day in that month) = 3,225.81 or if I had my slider on any day in November to have an output of (150,000/# of day in that month) = 5,000.

1 ACCEPTED SOLUTION
Super User

Got it. You want to override your slicer selection.

This might work:

``````DailySalesGoalAMT =
VAR CurrMonth = MAX ( 'Date'[MonthDate] )
RETURN
CALCULATE (
SUM ( 'Monthly Sales Goals 2021'[Sales Goal Amount] ),
ALL ( 'Date' ),
'Date'[MonthDate] = CurrMonth
) / [DaysInMonth]``````
6 REPLIES 6
Super User

If your date table is set up properly, then you should just need

``DailySalesGoalAMT = SUM ( 'Monthly Sales Goals 2021'[Sales Goal Amount] ) / [DaysInMonth]``

Set up properly means that the date table has a one-to-many relationship with your goals table from 'Date'[Date] to a Goals[Date] column that's a representative date from each month. You can add such a column by defining  a calculated column on that table like this:

``Date = DATEVALUE ( 'Goals'[Month] & " 1, 2021" )``
Helper II

The below screenshot is the new setup on my "Sales Goals" table. It includes the "date" column you suggested to add.

This is how my current date table is setup:

This is the relationship made between the two tables:

I am still having the issue of my measure summing each months daily sales goal amount within the sliders... Not sure what the issue is.

`DailySalesGoalAMT = SUM ( 'Monthly Sales Goals 2021'[Sales Goal Amount] ) / [DaysInMonth]`

Super User

What does your visual currently look like and what should it look like?

Helper II

The below chart in white represents what my Sales Goal table looks like. The Yellow column is just to show what the Daily Sales Goal Amount (sales goal amount/# of days in that month)

For this example, lets say we have the sliders from 1/1/2021 to 6/15/2021.

The result that should be shown is a matrix like below. Since Chris is the only sales rep with a sales goal for June it will show the daily sales goal amount for the most recent month on the slider (June).

The below chart is the output I am getting now. It it taking the sum of that "yellow" column (this column is not actually in my table. Just a representation to help you) for all months within the slider range.

All i want to see is the current month within the sliders daily sales goal amount.

Super User

Got it. You want to override your slicer selection.

This might work:

``````DailySalesGoalAMT =
VAR CurrMonth = MAX ( 'Date'[MonthDate] )
RETURN
CALCULATE (
SUM ( 'Monthly Sales Goals 2021'[Sales Goal Amount] ),
ALL ( 'Date' ),
'Date'[MonthDate] = CurrMonth
) / [DaysInMonth]``````
Helper II

YES! That works beautifully! Thank you so much

Announcements

#### 2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.