Skip to main content
cancel
Showing results forย 
Search instead forย 
Did you mean:ย 

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
RS_123
Frequent Visitor

How to remove incoming date filter when summing up monthly numbers to quarterly numbers

I have been trying to build a sales capacity model. The first few measures calculate monthly new hires, attrition, begnning sales headcount and ending headcount. I have connected the data table to sales roster table that has name, start date, end date, region, and quota. I am having trouble in summing up the new hires calculated at the month level into the quarter level. Please see screen shot below for the month, which works correctly and quarter, which does not work. The issue appears to be that I am not able to remove incoming month filter when I tried to sum up the new hires at the quarter level. Month Level.PNG

This is at the quarterly level. Please note the quarterly new hire is picking up the last month of the quarter new hire count.

 

Quarter level.PNG

 

To sum up at the quarter level, I am using the following DAX code. The measure [Status] returns 1 if the hire is a new hire, 0, the sales rep is existing, and -1 if the sales rep attritts. This measure is calculated for each rep in the sales roster at the month level. To calculate the new hire count, I use SUMX to add up the all the names in salesroster if [status] = 1. For quarter level calculation, I tried to remove the incoming filter by using All (SalesRoster) and adding another fillter to pick up the new hires only for the selected quarter. Please see code below.  Any help on how to fix the summation of quarterly into month would be greatly appreciated.

New Hire =
VAR _selectedcase = [SelectedCase]
VAR _noofrows = countrows ('Date')
 
Return if (_noofrows = 365,
// If date is at the year level, add all the months' new hire count
Calculate (SUMX (FILTER(SalesRoster,
[Case] = _selectedcase && [Status] = 1), [Status]),
All ('Date'[Month Number])),

// If date is at the quarter level, add 3 months of the quarter's new hires
If (_noofrows >= 90,
VAR _fiscalquarter = Selectedvalue ('Date'[Fiscal Quarter])
Return Calculate (SUMX (FILTER(SalesRoster,
[Case] = _selectedcase && [Status] = 1), [Status]),
All (SalesRoster),
'Date'[Fiscal Quarter] = _fiscalquarter),

// at the month level, just filter the sales roster for status =1
VAR _newhire = SUMX (FILTER(SalesRoster, [Case] = _selectedcase && [Status] = 1 ),
[Status])
Return _newhire
)
)



 
 

 

 

 

 



 

 

 

0 REPLIES 0

Helpful resources

Announcements
November Fabric Update Carousel

Fabric Monthly Update - November 2025

Check out the November 2025 Fabric update to learn about new features.

Fabric Data Days Carousel

Fabric Data Days

Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

Users online (25)