Suming total leavers by month - but then slicing that total by user selected slicer value?

[domdom]

Hi there - i'm quite new to power bi so this might be a simple issue to solve - but your help much appreciated!

I have two tables:

TABLE A   (Holding employee records - including leave dates/gender and other employee characteristics)

-----------

Id      Leave Date       Gender

1        10/01/2018      Male

2        12/01/2018      Male

3        20/01/2018      Female

4        01/02/2018      Female

5        07/02/2018      Female

6        23/02/2018      Female

7        23/02/2018      Male

8        03/03/2018      Female

TABLE B    (reach record represents a month of the year)

-----------

Month Start        Month End

01/01/2018        31/01/2018

01/02/2018        28/02/2018

01/03/2018        31/03/2018

In table B - I would like to add a new column "Total leavers by month" which would count the total leavers by month.  However - what I would also like to achieve is if a user was to then click a report slicer for Male gender only - it would only perform the monthly total count against those male gender employees from Table A.

E.g. when no slicer is selected - table B should be

---

Month Start        Month End     Total leavers by month

01/01/2018        31/01/2018     3

01/02/2018        28/02/2018     4

01/03/2018        31/03/2018     1

and if Male is selected from a report slicer - it should show:

---

Month Start        Month End     Total leavers by month

01/01/2018        31/01/2018     2

01/02/2018        28/02/2018     1

01/03/2018        31/03/2018     0

---

There may be any number of characteristics I want to filter in this way on - so it's important it remains flexiable and not hard coded in to DAX formula or in to the report filters (as I need report users to be able to change these during use).  Is this possible to achieve in anyway? 

 Many Thanks

[Ashish_Mathur · Accepted solution]

Hi,

You may refer to my solution here.

Hope this helps.

[domdom]

Thanks so much Ashish - i've accepted this as a solution to my question....

However - I've realised i still need help with this and need to provide a bit more detail about what I am trying to achieve.... If you have some time would you be able to see if you can help further with the following:

https://community.powerbi.com/t5/Desktop/Calculating-rolling-12-month-attrition-but-with-dynamic-sli...

Thanks

[Anonymous]

HI @domdom,

You can use only Table A if you wish,

In a visual table, place Leave Date as Date Hierarchy and keep only the year and month, and select Show items with no data to see the month with 0 female or male when users filter the visual.
And Count of Gender as third column.

You'll have something like this and you can filter by gender:

[domdom]

Thanks - however I need to do further calculations on this table such as a rolling 12 month average of the monthly leaver totals - which i'm not sure i'd be able to do without a separate table....