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, e...
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

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.

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

    Hi mikeborg82,

     

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

    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's avatar
      mikeborg82
      Advocate II
      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

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

        Hi mikeborg82,

         

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

         

        Regards