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.

v-juanli-msft's avatar
v-juanli-msft
Community Support
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.