Calculate date base on date row and create duplicates

[njxfoster]

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

SetupsStart	1/26/2024
SetupsStart	1/27/2024
SetupsStart	1/28/2024
SetupsStart	1/29/2024
AgentsAll In	1/30/2024

Is there anyway to achieve this ?

Thanks in advances!!!

[v-nuoc-msft · Accepted solution]

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]

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?

[lbendlin]

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