Help with Full Quarter Total

Hi Anonymous ,

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,

Hi Anonymous ,

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.

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.

 

 

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

 

Regards,

Dinesh