Forum Discussion

mikeborg82's avatar
mikeborg82
Advocate II
8 years ago
Solved

Terminations

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.

Need Help
  • mikeborg82's avatar
    mikeborg82
    8 years ago

    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

7 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    8 years ago

    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. :smileyhappy:

     

    Regards

    • mikeborg82's avatar
      mikeborg82
      Advocate II
      8 years ago

      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.  

       

      • mikeborg82's avatar
        mikeborg82
        Advocate II
        8 years ago

        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.