topic Re: Calculate date base on date row and create duplicates in DAX Commands and Tips

Calculate date base on date row and create duplicates

njxfoster — Thu, 08 Feb 2024 23:09:21 GMT

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

Re: Calculate date base on date row and create duplicates

lbendlin — Fri, 09 Feb 2024 02:21:22 GMT

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

Re: Calculate date base on date row and create duplicates

Anonymous — Fri, 09 Feb 2024 05:39:19 GMT

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.

Re: Calculate date base on date row and create duplicates

njxfoster — Tue, 05 Mar 2024 22:23:08 GMT

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?