Forum Discussion

jen8080's avatar
jen8080
Helper I
5 years ago
Solved

Creating new column and a calculated column

Hello-
I need to be able to pull the 'Live' Milestone Actual date into a column for each ID.
I then need to be able to pull the 'In Date' Milestone Actual date into a column for each ID.

Then I need to compare the dates, if the 'Live' Actual data is blank, it would be 'N/A', if the 'In Date' was blank it would be "Yes" and if the 'Live'<'In Date', "Yes", "No"

I made this in a matrix but need it in column because I can't get the measures to populate any charts.
Any help is appreciated!
Here is a sample the data

 

Need Help
  • DataInsights's avatar
    DataInsights
    5 years ago

    jen8080, try these calculated columns:

     

    Live Actual Date = 
VAR vID = Milestone[ID]
VAR vTargetRow =
    FILTER ( Milestone, Milestone[ID] = vID && Milestone[Milestone Name] = "Live" )
VAR vResult =
    MAXX ( vTargetRow, Milestone[Actual Date] )
RETURN
    vResult


In Date Actual Date = 
VAR vID = Milestone[ID]
VAR vTargetRow =
    FILTER ( Milestone, Milestone[ID] = vID && Milestone[Milestone Name] = "In Date" )
VAR vResult =
    MAXX ( vTargetRow, Milestone[Actual Date] )
RETURN
    vResult


Calc Column = 
VAR vLiveActualDate =
    Milestone[Live Actual Date]
VAR vInDateActualDate =
    Milestone[In Date Actual Date]
VAR vResult =
    SWITCH (
        TRUE (),
        ISBLANK ( vLiveActualDate ), "N/A",
        ISBLANK ( vInDateActualDate ), "Yes",
        vLiveActualDate < vInDateActualDate, "Yes",
        "No"
    )
RETURN
    vResult

     

     

3 Replies

  • DataInsights's avatar
    DataInsights
    Super User
    5 years ago

    jen8080, try these calculated columns:

     

    Live Actual Date = 
VAR vID = Milestone[ID]
VAR vTargetRow =
    FILTER ( Milestone, Milestone[ID] = vID && Milestone[Milestone Name] = "Live" )
VAR vResult =
    MAXX ( vTargetRow, Milestone[Actual Date] )
RETURN
    vResult


In Date Actual Date = 
VAR vID = Milestone[ID]
VAR vTargetRow =
    FILTER ( Milestone, Milestone[ID] = vID && Milestone[Milestone Name] = "In Date" )
VAR vResult =
    MAXX ( vTargetRow, Milestone[Actual Date] )
RETURN
    vResult


Calc Column = 
VAR vLiveActualDate =
    Milestone[Live Actual Date]
VAR vInDateActualDate =
    Milestone[In Date Actual Date]
VAR vResult =
    SWITCH (
        TRUE (),
        ISBLANK ( vLiveActualDate ), "N/A",
        ISBLANK ( vInDateActualDate ), "Yes",
        vLiveActualDate < vInDateActualDate, "Yes",
        "No"
    )
RETURN
    vResult

     

     

  • mhossain's avatar
    mhossain
    Solution Sage
    5 years ago

    jen8080 

    Not clear to me, do you need one row for one ID? Do you want to transform the sample data you provided, in the PowerBI how your table looks like?

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi jen8080,

    Did DataInsights 's suggestion help for your scenario? If this is a case, you can consider accepting this suggestion to help other users with the same requirement.
    If not, you can feel free to post here with detailed information and descriptions.

    Regards,

    Xiaoxin Sheng