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

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