Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculating month data from year data

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!
Need Help

3 Replies