Forum Discussion

jonas123's avatar
jonas123
Regular Visitor
6 years ago
Solved

Creating a custom column from existing table

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...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    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's avatar
az38
Community Champion
6 years ago

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