Calculating month data from year data

[sraizada]

Hi all,

I have a table with active headcount and terminations in one data set. It is an appended table from three year end snapshots. Based on this, I have calculated the average headcount and monthly terminations

Measure 1: AverageHeadcount =

var Heacount2018=[Headcount 2018]

var Headcount2019= [Headcount 2019]

var Headcount2020=[Headcount 2020]

return

VariableTable[AvgHeadcount]

Measure 2: 

MonthlyTerminations =

var Termination2018=MeasureTable[2018MonthlyCumlulativeTerminations]

var Termination2019= MeasureTable[2019MonthlyCumlulativeTerminations]

var Termination2020=MeasureTable[2020MonthlyCumlulativeTerminations]

return

VariableMonth[Terminations]

From this, I want to create a measure which will give me average headcount and monthly termination for a particular month.

I need to create 3 variables:

1. Variable or measure for average headcount - it takes the average of beginning year headcount and the latest month end headcount.

2. Variable or measure for monthly terminations which will basically filter out the variable for yearly terminations for that particular month and year

3. Variable or measure to average headcount for a particular month and year. (basically a filter of variable or measure month)

Any ideas on how to go about it? My data model has no relationships

Appreciate all your suggestions

thank you!

[amitchandak · Accepted solution]

Looking at this information , difficut to tell. Share a sample data and sample out. But if you data inf the format mentioed in my HR blog can get those easily.

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[v-diye-msft · Accepted solution]

Hi @sraizada 

you might consider creating pbix file that will contain some sample data (remove the confidential info), upload the pbix to onedrive or dropbox and share the link to the file. Please do not forget to describe the expected results based on this sample data.

[amitchandak · Accepted solution]

Looking at this information , difficut to tell. Share a sample data and sample out. But if you data inf the format mentioed in my HR blog can get those easily.

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[Greg_Deckler]

I suggest you post sample/example data and your expected result! 🙂 Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490