Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
Hi ,
I have headcount table where I need to create a calculated column for employee team of previous month. Please find sample data and the expected result.
Emp no | job effective date | team | previous team(calculated column) |
1 | 01/01/2022 | Sales | |
1 | 02/01/2022 | Marketing | Sales |
2 | 01/05/2022 | sales | |
2 | 02/01/2022 | sales | Sales |
2 | 03/12/2022 | Marketing | Sales |
any help on this would be much appreciated.
Thanks
Solved! Go to Solution.
Hi,
Please check the below picture and the attached pbix file.
It is for creating a new column.
Previous Team CC =
VAR _currentdate = Data[job effective date]
VAR _currentemp = Data[Emp no]
RETURN
SUMMARIZE (
FILTER (
Data,
Data[Emp no] = _currentemp
&& EOMONTH ( Data[job effective date], 0 ) = EOMONTH ( _currentdate, -1 )
),
Data[team]
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Schedule a short Teams meeting to discuss your question
Hi,
Please check the below picture and the attached pbix file.
It is for creating a new column.
Previous Team CC =
VAR _currentdate = Data[job effective date]
VAR _currentemp = Data[Emp no]
RETURN
SUMMARIZE (
FILTER (
Data,
Data[Emp no] = _currentemp
&& EOMONTH ( Data[job effective date], 0 ) = EOMONTH ( _currentdate, -1 )
),
Data[team]
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Schedule a short Teams meeting to discuss your question
User | Count |
---|---|
16 | |
15 | |
14 | |
12 | |
11 |
User | Count |
---|---|
19 | |
15 | |
14 | |
11 | |
9 |