Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
CiaraCaryl
Frequent Visitor

Help with Full Quarter Total

‎07-18-2025 09:02 AM

So I'm trying to get a total for salesreps quotas for the current quarter, including dates that havent happened yet so I can create a daily quota requirement and then a QTD quota based on the daily quota for sales reps to see how they are pacing during the quarter.

The formula I've been trying and playing around with is: CALCULATE([Sum of RSM Quotas], 'Quotas Unrelated'[Is this Qtr] = "TRUE") But it keeps giving me the full year total.

Or this formula ends up giving me the month's total so I'm assuming its trying to do QTD with only one date. CALCULATE(SUM('Quotas RSM'[Quota]), 'Combined Table'[Current Quarter] = TRUE())


In my tables, I have the sales reps quotas for each month with 1 date attached. I also have a date table with a relationship to the quota table. 

Heres a super simplified example of my quota data.

QuotaDateSales rep
52000    7/1/2025  rep 1    
980007/1/2025rep 2
330007/1/2025rep 3
560008/1/2025rep1
910008/1/2025rep 2
340008/1/2025rep 3
540009/1/2025

rep1

930009/1/2025rep 2
320009/1/2025rep 3
Message 1 of 8
532 Views
0
2 ACCEPTED SOLUTIONS
DataNinja777
Super User
Super User

‎07-20-2025 05:21 AM

Hi @CiaraCaryl ,

 

The issue you're encountering is a common one in DAX and relates to filter context. When you place a measure in a visual that has a date context (like a table row for July), the calculation is filtered to only include data from that month. To get the total for the entire quarter, you need a formula that can ignore the monthly filter from the visual and apply its own filter for the whole quarter.

 

The correct DAX measure to calculate the total quota for the entire current quarter is:

Full Quarter Quota =
CALCULATE (
    SUM ( 'Quotas RSM'[Quota] ),
    FILTER (
        ALL ( 'Date Table' ),
        'Date Table'[Year] = YEAR ( TODAY () )
            && 'Date Table'[Quarter of Year] = QUARTER ( TODAY () )
    )
)

This formula works by first using SUM to specify the aggregation of the quota column. The CALCULATE function then modifies the environment in which this sum is performed. The key is the ALL('Date Table') function, which removes any pre-existing filters from the Date Table, such as the filter for a specific month coming from your visual. After clearing the filters, the FILTER function applies a new context, keeping only the rows from your Date Table where the year and quarter match the current date. For today, July 20, 2025, it will find the total for Quarter 3 of 2025.

 

For this formula to work, your Date Table must have columns for the year and the quarter. If it doesn't, you can add them as calculated columns with these simple expressions: Year = YEAR('Date Table'[Date]) and Quarter of Year = QUARTER('Date Table'[Date]).

 

With the Full Quarter Quota measure created, you can now proceed to calculate the daily and pacing metrics you need. To find the daily quota requirement, you divide the full quarter's quota by the number of days in that quarter.

Daily Quota =
VAR CurrentQuarter = QUARTER(TODAY())
VAR CurrentYear = YEAR(TODAY())
VAR DaysInQuarter =
    COUNTROWS(
        FILTER(
            ALL('Date Table'),
            'Date Table'[Quarter of Year] = CurrentQuarter && 'Date Table'[Year] = CurrentYear
        )
    )
RETURN
    DIVIDE([Full Quarter Quota], DaysInQuarter)

Finally, to see how a sales rep is pacing against their goal, you can create a measure that shows what their quota attainment should be as of today. This is done by multiplying the daily quota requirement by the number of days that have already passed in the quarter.

QTD Pacing Quota =
VAR DaysPassed =
    COUNTROWS(
        DATESQTD('Date Table'[Date])
    )
RETURN
    [Daily Quota] * DaysPassed

 

Best regards,

View solution in original post

Message 4 of 8
428 Views
0
v-dineshya
Community Support
Community Support

‎07-21-2025 02:01 AM

Hi @CiaraCaryl ,

Thank you for reaching out to the Microsoft Community Forum.

 

Please follow below stesps.

 

1. Created Table "Quota" with sample data based on your data. and created " Date" calculated table, please refer snap.

 

vdineshya_0-1753086060869.pngvdineshya_1-1753086089831.png

 

 

2.  Created Calculated column "QuotaQuarterKey" in "Qota" table with below code.

 

        QuotaQuarterKey = "Q" & QUARTER('Quota'[Date]) & "-" & YEAR('Quota'[Date])
 
3.  Created relationship between  'Date'[Date] --> 'Quota'[Date]
 
4.  Created measure "Full Quarter Quota" with below DAX code.
 
Full Quarter Quota =
VAR SelectedQuarterKey =
    CALCULATE(
        MAX('Date'[QuarterKey]),
        'Date'[IsCurrentQuarter] = TRUE()
    )
RETURN
CALCULATE(
    SUM('Quota'[Quota]),
    FILTER(
        ALL('Quota'),
        'Quota'[QuotaQuarterKey] = SelectedQuarterKey &&
        'Quota'[Sales rep] = MAX('Quota'[Sales rep])
    )
)
 
5. Dragged the fields  'Quota'[Sales rep] and 'Quota'[Full Quarter Quota] into Table visual.
 
Please refer below output snap and attached PBIX file.
 
vdineshya_2-1753086616924.png

 

I hope this information helps. Please do let us know if you have any further queries.

 

Regards,

Dinesh

View solution in original post

Quarter.pbix
Message 5 of 8
374 Views
0
7 REPLIES 7
v-dineshya
Community Support
Community Support

‎07-21-2025 02:01 AM

Hi @CiaraCaryl ,

Thank you for reaching out to the Microsoft Community Forum.

 

Please follow below stesps.

 

1. Created Table "Quota" with sample data based on your data. and created " Date" calculated table, please refer snap.

 

vdineshya_0-1753086060869.pngvdineshya_1-1753086089831.png

 

 

2.  Created Calculated column "QuotaQuarterKey" in "Qota" table with below code.

 

        QuotaQuarterKey = "Q" & QUARTER('Quota'[Date]) & "-" & YEAR('Quota'[Date])
 
3.  Created relationship between  'Date'[Date] --> 'Quota'[Date]
 
4.  Created measure "Full Quarter Quota" with below DAX code.
 
Full Quarter Quota =
VAR SelectedQuarterKey =
    CALCULATE(
        MAX('Date'[QuarterKey]),
        'Date'[IsCurrentQuarter] = TRUE()
    )
RETURN
CALCULATE(
    SUM('Quota'[Quota]),
    FILTER(
        ALL('Quota'),
        'Quota'[QuotaQuarterKey] = SelectedQuarterKey &&
        'Quota'[Sales rep] = MAX('Quota'[Sales rep])
    )
)
 
5. Dragged the fields  'Quota'[Sales rep] and 'Quota'[Full Quarter Quota] into Table visual.
 
Please refer below output snap and attached PBIX file.
 
vdineshya_2-1753086616924.png

 

I hope this information helps. Please do let us know if you have any further queries.

 

Regards,

Dinesh

Quarter.pbix
Message 5 of 8
375 Views
0
v-dineshya
Community Support
Community Support
In response to v-dineshya

‎07-24-2025 10:48 AM

Hi @CiaraCaryl ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

 

Regards,

Dinesh

Message 6 of 8
305 Views
0
v-dineshya
Community Support
Community Support
In response to v-dineshya

‎07-28-2025 04:55 AM

Hi @CiaraCaryl ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

 

Regards,

Dinesh

Message 7 of 8
265 Views
0
v-dineshya
Community Support
Community Support
In response to v-dineshya

‎07-31-2025 07:53 AM

Hi @CiaraCaryl ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

 

Regards,

Dinesh

Message 8 of 8
240 Views
0
DataNinja777
Super User
Super User

‎07-20-2025 05:21 AM

Hi @CiaraCaryl ,

 

The issue you're encountering is a common one in DAX and relates to filter context. When you place a measure in a visual that has a date context (like a table row for July), the calculation is filtered to only include data from that month. To get the total for the entire quarter, you need a formula that can ignore the monthly filter from the visual and apply its own filter for the whole quarter.

 

The correct DAX measure to calculate the total quota for the entire current quarter is:

Full Quarter Quota =
CALCULATE (
    SUM ( 'Quotas RSM'[Quota] ),
    FILTER (
        ALL ( 'Date Table' ),
        'Date Table'[Year] = YEAR ( TODAY () )
            && 'Date Table'[Quarter of Year] = QUARTER ( TODAY () )
    )
)

This formula works by first using SUM to specify the aggregation of the quota column. The CALCULATE function then modifies the environment in which this sum is performed. The key is the ALL('Date Table') function, which removes any pre-existing filters from the Date Table, such as the filter for a specific month coming from your visual. After clearing the filters, the FILTER function applies a new context, keeping only the rows from your Date Table where the year and quarter match the current date. For today, July 20, 2025, it will find the total for Quarter 3 of 2025.

 

For this formula to work, your Date Table must have columns for the year and the quarter. If it doesn't, you can add them as calculated columns with these simple expressions: Year = YEAR('Date Table'[Date]) and Quarter of Year = QUARTER('Date Table'[Date]).

 

With the Full Quarter Quota measure created, you can now proceed to calculate the daily and pacing metrics you need. To find the daily quota requirement, you divide the full quarter's quota by the number of days in that quarter.

Daily Quota =
VAR CurrentQuarter = QUARTER(TODAY())
VAR CurrentYear = YEAR(TODAY())
VAR DaysInQuarter =
    COUNTROWS(
        FILTER(
            ALL('Date Table'),
            'Date Table'[Quarter of Year] = CurrentQuarter && 'Date Table'[Year] = CurrentYear
        )
    )
RETURN
    DIVIDE([Full Quarter Quota], DaysInQuarter)

Finally, to see how a sales rep is pacing against their goal, you can create a measure that shows what their quota attainment should be as of today. This is done by multiplying the daily quota requirement by the number of days that have already passed in the quarter.

QTD Pacing Quota =
VAR DaysPassed =
    COUNTROWS(
        DATESQTD('Date Table'[Date])
    )
RETURN
    [Daily Quota] * DaysPassed

 

Best regards,

Message 4 of 8
429 Views
0
lbendlin
Super User
Super User

‎07-18-2025 05:28 PM 
including dates that havent happened yet

To report on things that are not there you need to use disconnected tables and/or crossjoins

Message 3 of 8
463 Views
0
MasonMA
Memorable Member
Memorable Member

‎07-18-2025 09:19 AM

@CiaraCaryl 

 

Hi there, assuming your Date table hasn't been properly 'Marked as Date table', i would suggest using below logic for QTD in DAX:

Current Quarter Quota = 
VAR _CurrentYear = YEAR(TODAY())
VAR _CurrentQuarter = QUARTER(TODAY())

RETURN
CALCULATE(
    SUM(Quota[Quota]),
    FILTER(
        ALL(Quota),
        YEAR(Quota[Date]) = _CurrentYear &&
        QUARTER(Quota[Date]) = _CurrentQuarter
    )
)

 

Hope it helps:)

Message 2 of 8
521 Views
0

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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

View All