Forum Discussion

AnaAlbano's avatar
AnaAlbano
Helper I
5 years ago

Monthly Average Headcount

Hello Community! 

 

I´m starting my DAX journey and I wonder if you could help with this question: I´m trying to calculate the average headcount considering the beginning of the month and end of the month, I have a new dataset included in the data base for each month, which I use the 'report date´field to indicate the period. For example, July 1, all the details per employee line and the same for July 31st.

 

How could I do it?

 

Thank you,

Ana

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    AnaAlbano , for end of the month you can use closingbalancemonth with help from date table 

    or try lastnonblankvalue and  firstnonblankvalue

     

    example

    calculate( lastnonblankvalue('Date'[Date], Sum(Table[Headcount])) + firstnonblankvalue('Date'[Date], Sum(Table[Headcount])) , allexpect(Table, Table[Month Year]))/2

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Community Support
    5 years ago

    Hi, AnaAlbano 

     

    Could you please consdier sharing more details about it or a simple sample file without any sesentive information  so that i may work out with a workaround?

     

     

    Best Regards,
    Community Support Team _ Zeon Zheng
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.