Multiple Date Filter

DAX DAX Calculations
1

Hi team,

I have scenario where I need to calculate the number of employees who are in new role in Last 12 months and excluding whos job role date in last 12 months.

I have written a DAX code where am getting the Total correct - 116 for 12 Months, but when I see that inside the table the subtotal are summing to 97 which is incorrect, I have kept relative date filter in filter panel just to see the last 12 months…

Filter test.pbix (144.0 KB)

2

Hi @Dharma, I believe your post doesn’t have a question. Can you provide what you need to be solved in this scenario? Thanks

3

Well my question is when I sum up the numbers it is coming to 97 not 116 , just wanted to understand what wrong am doing in my DAX logic.

4

Hi @Dharma, please check this post by @Greg on Fixing incorrect totals: Fix Incorrect Totals - DAX Patterns - Enterprise DNA Forum

Hope you will find it helpful.

5

Hello Team,

Kindly review my problem statement I have clearly mentioned my problem and the issue is not related to Total but the values inside the table…

6

Hi @Dharma - I have looked into the Solution and don’t really understand what are you trying to achieve.

Please clarify, what do you mean by the statement “excluding whos job role date in last 12 months.”

Thanks
Ankit J

7

Hello @ankit ,

Request you to please refer my first post and PBIX file again I have clearly mentioned my scenario am looking at employees who are in new role in last 12 months and who’s job role date not falling in last 12 months.

I have already got the right solution as 116 as Total but when am seeing that in table the values are not summing to 116 but they are summing to 97 this is my problem, I want to understand what mistake I have done in the DAX code I have written.

Thanks,
Dharmendar S
Filter test.pbix (143.9 KB)

8

Hi @Dharma - Rewent through your problem. The Total count will not match with the table rows individual count. This is because of change in the evaluation context.

While counting values for individual row, Evaluation context is the “New Role Entry Date” and corresponding “Join Date” and based on this the output is calculated while during Total there is no “New Role Entry Date” or “Joining Date” context and Power BI takes the maximum and minimum values available and calculate based on that.

This is also clearly explained in Blog post by Greg and shared by EDNA team Fix Incorrect Totals

Hope the explanation is helpful.

Thanks
Ankit J

9

Hi @ankit as I mentioned above the issue is about not Total but the values within the table.

Anyway I figured out solution by myself thanks for your effort…

10

Hi @Dharma - Out of curiosity, can you share the Solution you found. It will be helpful for me/others in future.

Thanks
Ankit J