Female representation within total headcount, 6 months prior - dax formula

[Anonymous]

Hi Everone,

I have a report called worker data extract which is an appended long query, containing headcount-related demographic information and in a field called [Date] the date when the report was pulled which is the last day of each month. I have a related table ("Dim_Dates") in which all the days of the year are paired with a given label called [Month] in the order like the following:

01/07/2016 - 31/07/2016 as "F17 P01"

01/08/2016 - 31/08/2016 as "F17 P02"

...

01/10/2017 - 31/10/2017 as "F18 P04"

I added a column Month to my worker data extract which is =RELATED(Dim_Date[Month])

I have a bunch of dropdown filters in my report on my dashboard to alter filter context, [Month] included. If I select "F18 P04" as a Month on the filter I would like to see the Female represantation of the total headcount for 6 month before, as it was on "F17 P10".

Could you please tell me why this formula below is not working?

Female % 6 months prior =
VAR d =
    MAX ( 'Fact_Worker Data Extract'[Date] )
RETURN
    DIVIDE (
        CALCULATE (
            DISTINCTCOUNT ( 'Fact_Worker Data Extract'[_Employee ID] ),
            ALL ( 'Fact_Worker Data Extract'[Month] ),
            'Fact_Worker Data Extract'[Date] = EOMONTH ( d, -6 ),
            'Fact_Worker Data Extract'[Gender] = "Female"
        ),
        CALCULATE (
            DISTINCTCOUNT ( 'Fact_Worker Data Extract'[_Employee ID] ),
            ALL ( 'Fact_Worker Data Extract' ),
            'Fact_Worker Data Extract'[Date] = EOMONTH ( d, -6 )
        )
    )

[v-ljerr-msft]

Hi @Anonymous,

Could you try the formula below to see if it works in your scenario? 

Female % 6 months prior =
VAR d =
    MAX ( 'Fact_Worker Data Extract'[Date] )
RETURN
    DIVIDE (
        CALCULATE (
            DISTINCTCOUNT ( 'Fact_Worker Data Extract'[_Employee ID] ),
            ALL ( 'Fact_Worker Data Extract' ),
            'Fact_Worker Data Extract'[Date] = EOMONTH ( d, -6 )
                && 'Fact_Worker Data Extract'[Gender] = "Female"
        ),
        CALCULATE (
            DISTINCTCOUNT ( 'Fact_Worker Data Extract'[_Employee ID] ),
            ALL ( 'Fact_Worker Data Extract' ),
            'Fact_Worker Data Extract'[Date] = EOMONTH ( d, -6 )
        )
    )

Regards

[Anonymous]

I would need to first somehow filter for the records which have a 6 months older month end date in their Date field and then do the divison. Is that possible?

[v-ljerr-msft]

Hi @Anonymous,

I would need to first somehow filter for the records which have a 6 months older month end date in their Date field and then do the divison. Is that possible?

---

In this scenario, you should be able to add other filters you need for calculation. The formula below is for your reference. 

Female % 6 months prior =
VAR d =
    MAX ( 'Fact_Worker Data Extract'[Date] )
VAR o =
    FIRSTNONBLANK ( 'Fact_Worker Data Extract'[organization], 1 )
RETURN
    DIVIDE (
        CALCULATE (
            DISTINCTCOUNT ( 'Fact_Worker Data Extract'[_Employee ID] ),
            ALL ( 'Fact_Worker Data Extract' ),
            'Fact_Worker Data Extract'[Date] = EOMONTH ( d, -6 )
                && 'Fact_Worker Data Extract'[Gender] = "Female"
                && 'Fact_Worker Data Extract'[organization] = o
        ),
        CALCULATE (
            DISTINCTCOUNT ( 'Fact_Worker Data Extract'[_Employee ID] ),
            ALL ( 'Fact_Worker Data Extract' ),
            'Fact_Worker Data Extract'[Date] = EOMONTH ( d, -6 )
                && 'Fact_Worker Data Extract'[organization] = o
        )
    )

Regards

[Anonymous]

Apologies, I used the wrong word, slicers, I mean.

So I have like 8 different slicers as well for users in order to select their own prefered combination, and I need the formula to work with those slicers as well. Is that possible?

[v-ljerr-msft]

Hi @Anonymous,

Could you try the formula below to see if it works? 

Female % 6 months prior =
VAR d =
    MAX ( 'Fact_Worker Data Extract'[Date] )
RETURN
    DIVIDE (
        CALCULATE (
            DISTINCTCOUNT ( 'Fact_Worker Data Extract'[_Employee ID] ),
            ALL ( 'Fact_Worker Data Extract'[Date] ),
            'Fact_Worker Data Extract'[Date] = EOMONTH ( d, -6 )
                && 'Fact_Worker Data Extract'[Gender] = "Female"
        ),
        CALCULATE (
            DISTINCTCOUNT ( 'Fact_Worker Data Extract'[_Employee ID] ),
            ALL ( 'Fact_Worker Data Extract'[Date] ),
            'Fact_Worker Data Extract'[Date] = EOMONTH ( d, -6 )
        )
    )

Regards

[Anonymous]

Hi,

Unfortunately that does not work, it returns blank.

[v-ljerr-msft]

Hi @Anonymous,

Could you share a sample pbix file(with just some sample/mock data) which can reproduce the issue, so that I can further assist on this issue? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. 

Regards

[Anonymous]

Hi again,

Thanks for the support and sorry for the late response - the issue still persists.

I created the pbix file to replicate the error. You may reach it on the following link:

https://drive.google.com/open?id=12QdDLQLMxHi9sGk4kMxRW0EK9fP7dM07

Many thanks in advance for your response!

Best regards,

György

[Anonymous]

Hi,

Thank you very much. That seems to work for one metric, but I would need to filter it further, broken down by different levels of the organization, etc. and with ALL() applied that can't happen. What if I need to show it as a table, broken down by another field's value in my dataset?