Terminations

[mikeborg82]

I have fact table with termination data for all fo 2016 and YTD 2017.  I have a date dimension table joined to the term data.  I want to show things like Terms YTD, Terms QTD, Terms YOY, Terms MOM, etc.  I am having trouble with the DAX expressions.  Any idea of a good place to start?  I would like to also have a calculation that tells me YTD turnover rate ( YTD terms/average headcount for the year,, etc..).. 

Thanks.

[v-ljerr-msft]

Hi @mikeborg82,

Data Analysis Expressions (DAX) includes time intelligence functions to support the needs of Business Intelligence analysis by enabling you to manipulate data using time periods, including days, months, quarters, and years, and then build and compare calculations over those periods.

Or you can refer to this article to implement time-related calculations without relying on DAX time intelligence functions.

If you still have trouble with the DAX expressions, just post your table structures with some sample/mock data, and the expected result against the data? So that we can further assist on this issue. 

Regards

[mikeborg82]

Thanks for the reply.  So I do have a pretty useful resource guide (DAX Patterns).  One thing I am trying to do is find average headcount by snapshot date YTD.  

Without looking YTD, I had the following DAX:

Average Headcount = CALCULATE(COUNT('Workforce Snapshots'[Employee Number]),DISTINCT('Workforce Snapshots'[Source.Name]))/DISTINCTCOUNT('Workforce Snapshots'[Source.Name])

Now that I want to use snapshots from many years, I want to be able to find average headcount by YTD, or other date time frames.  YTD would be good starting point.  I can't seem to come up with a good solution.  

[Ashish_Mathur]

Hi,

Share the link from where i can download your Excel data. Also, please show the exact result you are expecting.

[mikeborg82]

Avg YTD hc = TOTALYTD(CALCULATE(COUNT('Workforce Snapshots'[Employee Number]),DISTINCT('Workforce Snapshots'[Source.Name]))/DISTINCTCOUNT('Workforce Snapshots'[Source.Name]),'Workforce Snapshots'[Snapshot Date])

I tried this, but it does not seem to work.  I get no syntax errors, but it does not calculate right.

[v-ljerr-msft]

Hi @mikeborg82,

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

Avg YTD hc =
CALCULATE (
    CALCULATE (
        COUNT ( 'Workforce Snapshots'[Employee Number] ),
        DISTINCT ( 'Workforce Snapshots'[Source.Name] )
    )
        / DISTINCTCOUNT ( 'Workforce Snapshots'[Source.Name] ),
    FILTER (
        ALL ( 'Workforce Snapshots' ),
        'Workforce Snapshots'[Snapshot Date]
            <= MAX ( 'Workforce Snapshots'[Snapshot Date] )
            && YEAR ( 'Workforce Snapshots'[Snapshot Date] )
                = YEAR ( MAX ( 'Workforce Snapshots'[Snapshot Date] ) )
    )
)

Regards

[mikeborg82 · Accepted solution]

Thanks for the responses.  I create the snapshot date by each time I save a file for the month in a folder, I create that column based on those fields.  What I didn't do was change the format to date, so while there was no syntax issues, I couldn't get anything to accurately calculate.  I changed the format to date, and it seems to work fine now.  S

[v-ljerr-msft]

Hi @mikeborg82,

Great to hear the problem got resolved! Could you accept your reply above as solution to close this thread? 

Regards