Forum Discussion

nareshr89's avatar
nareshr89
Helper II
3 years ago
Solved

Headcount Calculation

I need help with a scenario with the headcount, where the numbers given under Opening is picked as is at monthly level but when its quarterly level we need to maintain the Jan data as Opening and Clo...
  • tamerj1's avatar
    3 years ago

    Hi nareshr89 

    Apologies for the late reply. I was out of office all day. 
    Please try by replacing SELECTEDVALUE ( Dim_time[MonthLoad] ) with MIN ( Dim_time[MonthLoad] ) for Opening HC and MAX ( Dim_time[MonthLoad] ) for Closing HC

  • nareshr89's avatar
    3 years ago

    Thanks for reverting tamerj1.

    Found the solution using similar logic for the YearMonth ID.

     

    F_OpeningHC =
    var yr = SELECTEDVALUE(Dim_time[Year])
    var ml = SELECTEDVALUE(Dim_time[MonthLoad])
    VAR yearmonth = CALCULATE(MIN(sf_CSS[time_id]),sf_CSS[sf_year]=yr,sf_CSS[sf_month_load]=ml)

     

    Return
    CALCULATE(SUM(sf_CSS[hc_opening]),sf_CSS[sf_year]=yr,sf_CSS[sf_month_load]=ml,sf_CSS[time_id]= yearmonth)

     

     

    F_ClosingHC =

    var yr = SELECTEDVALUE(Dim_time[Year])
    var ml = SELECTEDVALUE(Dim_time[MonthLoad])
    VAR yearmonth = CALCULATE(MAX(sf_CSS[time_id]),sf_CSS[sf_year]=yr,sf_CSS[sf_month_load]=ml)

    Return
    CALCULATE(SUM(sf_CSS[hc_closing]),sf_CSS[sf_year]=yr,sf_CSS[sf_month_load]=ml,sf_CSS[time_id]=yearmonth)