cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gyorgykovacs Regular Visitor
Regular Visitor

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

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 )
        )
    )

9 REPLIES 9
v-ljerr-msft Super Contributor
Super Contributor

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

Hi @gyorgykovacs,

 

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

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

gyorgykovacs Regular Visitor
Regular Visitor

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

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?

gyorgykovacs Regular Visitor
Regular Visitor

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

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 Super Contributor
Super Contributor

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

Hi @gyorgykovacs,



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. Smiley Happy

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

gyorgykovacs Regular Visitor
Regular Visitor

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

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 Super Contributor
Super Contributor

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

Hi @gyorgykovacs,

 

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

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

gyorgykovacs Regular Visitor
Regular Visitor

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

Hi,

 

Unfortunately that does not work, it returns blank.

v-ljerr-msft Super Contributor
Super Contributor

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

Hi @gyorgykovacs,

 

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. Smiley Happy

 

Regards

gyorgykovacs Regular Visitor
Regular Visitor

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

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