Monthly Average Headcount

[AnaAlbano]

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

[v-angzheng-msft]

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.

[amitchandak]

@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