cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
Tarun0055
Regular Visitor

Previous month calculated column for Text fields

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 nojob effective dateteam previous team(calculated column)
101/01/2022Sales 
102/01/2022MarketingSales
201/05/2022sales 
202/01/2022salesSales
203/12/2022MarketingSales

any help on this would be much appreciated.

Thanks 

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi, 

Please check the below picture and the attached pbix file.

It is for creating a new column.

 

Untitled.png

 

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.


Go to My LinkedIn Page


View solution in original post

1 REPLY 1
Jihwan_Kim
Super User
Super User

Hi, 

Please check the below picture and the attached pbix file.

It is for creating a new column.

 

Untitled.png

 

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.


Go to My LinkedIn Page


Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors