Forum Discussion

soji's avatar
soji
Frequent Visitor
5 years ago

Add new column using DAX 1

Hi I have a table in power BI as mentioned below in yellow highlighted columns and i would like to add a new column using DAX as shown in blue color . Can you please help.   Thanks Soji
wdx223_Daniel's avatar
wdx223_Daniel
Icon for Community Champion rankCommunity Champion
5 years ago

soji try this code

=
VAR _ChangeLineAfter =
    TOPN (
        1,
        FILTER (
            Table,
            Table[Employee Name] = EARLIER ( Table[Employee Name] )
                && Table[Month & Year] > EARLIER ( Table[Month & Year] )
                && Table[Department Change] = "YES"
        ),
        Table[Month & Year], ASC
    )
VAR _ChangeLineBefore =
    TOPN (
        1,
        FILTER (
            Table,
            Table[Employee Name] = EARLIER ( Table[Employee Name] )
                && Table[Month & Year] <= EARLIER ( Table[Month & Year] )
                && Table[Department Change] = "YES"
        ),
        Table[Month & Year]
    )
RETURN
    COALESCE (
        MAXX ( _ChangeLineBefore, Table[New Department] ),
        MAXX ( _ChangeLineAfter, Table[Old Department] )
    )