Avoid Month filter in aggregation

Question

Hello dear members.&nbsp;I have a manpower budget vs actual report and i have a problem when i am adding the aspect of time into my table.&nbsp;I have the total new hires by job position and I compare them with the budgeted ones. So if I have for the first semester 5 in total budgeted positions for IT Consultant&nbsp; an I have hired&nbsp; 3 Consultants everything in my table is fine. But when i am adding the month parameter i am getting wrong results if i want to play with the filters.&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; BUDGETED&nbsp; ACTUAL&nbsp; RemainingIT CONSULTANT&nbsp; &nbsp; &nbsp; &nbsp; 5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2For example. I might have budgeted 1 consultant in January 3 in February and&nbsp; 1 in April and i have hired 1 consultants in February and 2 in March. The month breaks the hires and the budgeted positions by month and i am not getting a match of the positions because we hired them a different month of the budgeted.&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;BUDGETED&nbsp; ACTUAL&nbsp; RemainingIT CONSULTANTJanuary&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1February&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2March&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; -2April&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp;So my problem is when I am adding a filter on the budgeted column (is not blank)&nbsp; in order to see the actual hires for the budgeted positions.&nbsp; In this case i am missing the new employees that was hired in a different month that the budgeted. How can overcome this problem??Thank you very much!!

johnt75 · Answer

It sounds like you really want the running totals to be displayed so that you're showing all the positions which have been budgeted for or filled up to the month in question. You could try something likeTotal Budgeted =
var maxDate = MAX('Date'[Date])
return CALCULATE( SUM('Table'[Budgeted]), REMOVEFILTERS('Date'), 'Date'[Date] &lt;= maxDate )

Total Actual =
var maxDate = MAX('Date'[Date])
return CALCULATE( SUM('Table'[Actual]), REMOVEFILTERS('Date'), 'Date'[Date] &lt;= maxDate )

Total Remaining =
[Total Budgeted] - [Total Actual]

dimitris_kats · Answer

Thank you very much&nbsp;johnt75&nbsp; for replying to me.&nbsp;Unfortunately I am getting wrong results for the actual hires. I am getting much more than I should.What is the problem?

johnt75 · Answer

Is there prior year data for hires which is being included?&nbsp;Can you show the relationships in your model as well as the results you are expecting and the results you are getting ?

Forum Discussion

Avoid Month filter in aggregation

3 Replies

Related content

Issue with Aggregation and Filtering

Data Aggregation issue

How to avoid Line Chart doing unwanted aggregation?

how to avoid aggregation in table

Avoid Aggregation for Gauge