Forum Discussion

mikeborg82's avatar
mikeborg82
Icon for Advocate II rankAdvocate II
9 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
    9 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
Icon for Microsoft Employee rankMicrosoft Employee
9 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
Icon for Advocate II rankAdvocate II
9 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
    Icon for Advocate II rankAdvocate II
    9 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
      Icon for Microsoft Employee rankMicrosoft Employee
      9 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
        Icon for Advocate II rankAdvocate II
        9 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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    9 years ago

    Hi,

     

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