Creating a custom column from existing table

[jonas123]

Hi!

I'm quite new to Power BI and Power Query and I've managed to run into a problem.

I am trying to visualize data about employees. In the employee table I have something like this:

Name	Hired	Left the company	Favourite color
Jonas	2018		Red
Jeff	2017	2019	Red

Then what I want to do is track the favourite color of the employees for every year. (Assuming of course that a person has the same favourite color during employment). The basic idea I have to do this is to create a new table like this:

Year	People who like red
2017	1
2018	2
2019	2
2020	1

My question is: How do I write the M-function to create the "People who like red"-column?

Or is this the wrong way to go about visualizing this data?

Thank you in advance!

[v-juanli-msft · Accepted solution]

Hi @jonas123 

Create a date table

Date = ADDCOLUMNS(CALENDAR(DATE(MIN('Table'[Hired]),1,1),TODAY()),"year",YEAR([Date]))

Create a measure

Measure =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Name] ),
    FILTER (
        'Table',
        'Table'[Hired]
            <= MAX ( 'Date'[year] )
            && (
                'Table'[Left the company]
                    >= MAX ( 'Date'[year] )
                    || 'Table'[Left the company]
                        = BLANK ()
            )
    )
)

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[az38]

Hi @jonas123 

I think DAX usage would be better idea for your task

First, create a table with years

CalendarYearTable = GENERATESERIES(MIN('Table'[Hired]), YEAR(TODAY()))

next, in this new table create a measure 

People who like red = 
calculate(COUNTROWS('Table'),
FILTER(ALL('Table'),
'Table'[Favourite color]="Red" && 
'Table'[Hired]<=SELECTEDVALUE(CalendarYearTable[Value]) && 
('Table'[Left the company]>=SELECTEDVALUE(CalendarYearTable[Value]) || ISBLANK('Table'[Left the company]))
))

see the pbix-file in attach