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 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

     

     

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