Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

YTD Headcount

Hello, I am trying to calculate YTD % increase/decrease for headcount.  In example below, for month end of July 2022, I would calculate YTD % by doing 506-500/500 and multiplying by 100 to get 1.2% increase.  How can I achieve this using DAX measure?  Keeping in mind that the "current headcount" will change as months go by (Aug, Sept and so on) but the Jan value will always remain 500.

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    According to your error, I think you may compare text data with number data in your measure. Please make sure the data format of your [Year] and [MonthOfYear].  Both YEAR() OR MONTH() function will return number type data.

    If your [MonthOfYear] is in format like "January"..., you will get error. You can add a month column and year column in calendar table by Month() and Year() function. Then use them in your code.

    Start Month =
CALCULATE (
    [Headcount],
    FILTER (
        Calendar,
        YEAR ( Calendar[Date] ) = YEAR ( TODAY () )
            && MONTH ( Calendar[Date] ) = 1
    )
)
    Last Month =
CALCULATE (
    [Headcount],
    FILTER (
        Calendar,
        YEAR ( Calendar[Date] ) = YEAR ( TODAY () )
            && MONTH ( Calendar[Date] )
                = MONTH ( TODAY () ) - 1
    )
)

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

8 Replies

  • nleuck_101's avatar
    nleuck_101
    Continued Contributor
    4 years ago

    Anonymous 
    How is your data structured? Do you have a Date table?

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      nleuck_101  Yes, I have a date table that I use, see below.

  • nleuck_101's avatar
    nleuck_101
    Continued Contributor
    4 years ago

    Anonymous 

    You could try something creating two measure, one for last month and another for start month. Maybe something like this will work:

    Last Month = CALCULATE(SUM(Table[Headcount]), Calendar[Year] = YEAR(Today()) && Calendar[Month] = Month(Today())-1

     

    Start Month = CALCULATE(SUM(Table[Headcount]), Calendar[Year] = YEAR(Today()) && Calendar[Month] = 1

     

    Your final measure woudl be to find the YTD % growth:

    YTD % Growth = DIVIDE(Last Month - Start Month, Start Month, 0)

    Hope this works!

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi nleuck_101 , I tried the recommendation and recieved this error.

      • nleuck_101's avatar
        nleuck_101
        Continued Contributor
        4 years ago

        Anonymous 

         

        Trying putting quotes " " around the 1 and see if that works.

        Start Month = CALCULATE(SUM(Table[Headcount]), Calendar[Year] = YEAR(Today()) && Calendar[Month] = "1"