Forum Discussion

njxfoster's avatar
njxfoster
Icon for Helper I rankHelper I
2 years ago
Solved

Calculate date base on date row and create duplicates

Hi guys

 

 

I want to create duplicate row which show duration of a event.

 

For example, "SetupsStart"=1/26    "AgentsAll In" =1/30

 

I want to create extra rows which cover the period which the result will come out as

 

SetupsStart1/26/2024
SetupsStart1/27/2024
SetupsStart1/28/2024
SetupsStart1/29/2024
AgentsAll In1/30/2024

 

Is there anyway to achieve this ?

 

Thanks in advances!!!

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi njxfoster 

     

    I have provided a way to solve your problem:

     

    Here's some dummy data

     

    "Table"

     

    You can create a new date table with the time range "01/26/2024" to "01/30/2024".

     

     

    Date = CALENDAR("1/26/2024", "1/30/2024")

     

     

    Create a measure. Determine the date range and fill in the value.

    Event = 
var start_date = 
    CALCULATE(
        MAX('Table'[Date]), 
        FILTER(
            ALL('Table'), 
            'Table'[Event] = "SetupsStart"
        )
    )
var end_date = 
    CALCULATE(
        MIN('Table'[Date]), 
        FILTER(
            ALL('Table'), 
            'Table'[Event] = "AgentsALL In"
        )
    )
RETURN 
    IF(
        SELECTEDVALUE('Date'[Date]) >= start_date 
        && 
        SELECTEDVALUE('Date'[Date]) <> end_date, 
        "SetupsStart", 
        "AgentsALL In"
    )

     

    Here is the result.

     

     

    Regards,

    Nono Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

3 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    Once you have that, what will be your next step?  Some sort of count of days per event?

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi njxfoster 

     

    I have provided a way to solve your problem:

     

    Here's some dummy data

     

    "Table"

     

    You can create a new date table with the time range "01/26/2024" to "01/30/2024".

     

     

    Date = CALENDAR("1/26/2024", "1/30/2024")

     

     

    Create a measure. Determine the date range and fill in the value.

    Event = 
var start_date = 
    CALCULATE(
        MAX('Table'[Date]), 
        FILTER(
            ALL('Table'), 
            'Table'[Event] = "SetupsStart"
        )
    )
var end_date = 
    CALCULATE(
        MIN('Table'[Date]), 
        FILTER(
            ALL('Table'), 
            'Table'[Event] = "AgentsALL In"
        )
    )
RETURN 
    IF(
        SELECTEDVALUE('Date'[Date]) >= start_date 
        && 
        SELECTEDVALUE('Date'[Date]) <> end_date, 
        "SetupsStart", 
        "AgentsALL In"
    )

     

    Here is the result.

     

     

    Regards,

    Nono Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

    • njxfoster's avatar
      njxfoster
      Icon for Helper I rankHelper I
      2 years ago

      Hi Thank you for your reply! I need a solution that can cover all these event. The solution you provided only cover "SetupStart"and "AgentsAll in". Is there a solution that can cover all the other events (eg LeapStat, PrepStart etc) as well as other events that get added in the future?