topic Re: Avoid Month filter in aggregation in DAX Commands and Tips

Avoid Month filter in aggregation

Dimitris_Kats — Mon, 20 Jun 2022 06:36:30 GMT

Hello dear members.

I have a manpower budget vs actual report and i have a problem when i am adding the aspect of time into my table.

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 an I have hired 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.
BUDGETED ACTUAL Remaining
IT CONSULTANT 5 3 2

For example. I might have budgeted 1 consultant in January 3 in February and 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.

BUDGETED ACTUAL Remaining
IT CONSULTANT
January 1 1
February 3 1 2
March 2 -2
April 1 1

So my problem is when I am adding a filter on the budgeted column (is not blank) in order to see the actual hires for the budgeted positions. 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!!

Re: Avoid Month filter in aggregation

johnt75 — Mon, 20 Jun 2022 09:18:06 GMT

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 like

Total Budgeted = var maxDate = MAX('Date'[Date]) return CALCULATE( SUM('Table'[Budgeted]), REMOVEFILTERS('Date'), 'Date'[Date] <= maxDate ) Total Actual = var maxDate = MAX('Date'[Date]) return CALCULATE( SUM('Table'[Actual]), REMOVEFILTERS('Date'), 'Date'[Date] <= maxDate ) Total Remaining = [Total Budgeted] - [Total Actual]

Re: Avoid Month filter in aggregation

Dimitris_Kats — Mon, 20 Jun 2022 11:41:02 GMT

Thank you very much johnt75 for replying to me.

Unfortunately I am getting wrong results for the actual hires. I am getting much more than I should.
What is the problem?

Re: Avoid Month filter in aggregation

johnt75 — Mon, 20 Jun 2022 12:43:37 GMT

Is there prior year data for hires which is being included?

Can you show the relationships in your model as well as the results you are expecting and the results you are getting ?