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
DSwezey
Helper III
Helper III

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.

 

DSwezey_1-1637090984794.png

 

DSwezey_0-1637090976717.png

 

1 ACCEPTED SOLUTION

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]

View solution in original post

6 REPLIES 6
AlexisOlson
Super User
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" )

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

 

DSwezey_0-1637162819581.png

 

This is how my current date table is setup:

DSwezey_1-1637163080049.png

 

This is the relationship made between the two tables:

 

DSwezey_2-1637163168481.png

 

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]

 

 

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

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)

DSwezey_2-1637164583385.png

 

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

DSwezey_5-1637164950862.png

 

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).

DSwezey_4-1637164698565.png

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. 

DSwezey_3-1637164646292.png

 

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

 

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]

YES! That works beautifully! Thank you so much

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.

Top Solution Authors
Top Kudoed Authors