Calculation over time with multiple measures

[dwilson91]

I STILL NEED HELP IF ANYONE CAN HELP PLEASE...  The ultimate visual I need is a bar chart, not a matrix.  The matrix is just here to show you what results I'm looking for. I wish I could attach my workbook but I don't have that option - sorry!

************************************************************************************************************************

First time poster here.  Not sure if the subject is descriptive of my quandary or if some other post covers this. For that I apologize!  Also, this may be very simple for you but I'm stumped. And for that I apologize again!

Here are the context and goal:

I have a data set with work orders for rail tracks that cause trains to slow down, called a slow zone. Each of the work orders has a Date Reported (opened) and a Date Completed (blank if still active) on them. The work orders could originate from different departments at different times, i.e. one could come from construction on the track itself on 1/31/2021, while the other could come from signals on the track on 10/22/22 and both might still be active for two different purposes. So there could be multiple work orders active for the same length of track at the same time. There may also be one work order that was active at one point in time for a length of track but has since been completed, while another is still active. I am trying to show the trend of LENGTH OF TRACK over time that has had ACTIVE work orders on it (ultimately just a bar chart) to show slow zones. Because of this, I don't want to count the length of track multiple times if it had multiple active work orders on it.  I just want to count the length of track once if it had ANY active work order on it at that time.  See below for detailed data and attachments.

Here are the details:

I have created two measures for Total Number of Active Slow Zones and Total Length of Active Slow Zones over time using my generic date table as follows:

Total No of Active Slow Zones = 
VAR Currentdate = MAX('Date Table'[Select Date])
var ActiveSlowZones =
Calculate(
    COUNTROWS('Track Speed Limits'),
    all('Date Table'),
    'Date Table'[Select Date]<=Currentdate,
    (isblank('Track Speed Limits'[Date Completed])||'Track Speed Limits'[Date Completed]>=Currentdate)
    &&'Track Slow Zones'[Date Reported]<=Currentdate
)
var Result = 
if(
    YEAR(Currentdate)<=YEAR(TODAY()),
    ActiveSlowZones
)
RETURN
Result

Total Length of Active Slow Zones = 
VAR Currentdate = MAX('Date Table'[Select Date])
var ActiveSlowZones =
Calculate(
    SUM('Track Speed Limits'[Length]),
    all('Date Table'),
    'Date Table'[Select Date]<=Currentdate,
    (isblank('Track Speed Limits'[Date Completed])||'Track Speed Limits'[Date Completed]>=Currentdate)
    &&'Track Slow Zones'[Date Reported]<=Currentdate
)
var Result = 
if(
    YEAR(Currentdate)<=YEAR(TODAY()),
    ActiveSlowZones
)
RETURN
Result

But since I don't want to count the length multiple times based on multiple work orders, I tried to simply divide the Total Length by Total Number.  This only works on the rows when I include the Track ID in a matrix, but does not work for the total length.

Total Length by Track ID = [Total Length of Active Slow Zones]/[Total No of Active Slow Zones]

Basically, I want to get the SUM of the Total Length of Active Slow Zones/Total No of Active Slow Zone PER Track ID for each period in time, as is shown below:

Since I'm not able to attach anything, I'm pasting some sample data below.

Work Order	Track ID	Length	SZ Status	Owning Area	Date Reported	Date Completed
9900000	TRK01	114	In Effect	TRACK	4/4/2023
9900001	TRK02	109	In Effect	TRACK	4/4/2023
9900004	TRK05	259	Closed/Completed	CONST	3/21/2023	3/24/2023
9900005	TRK06	260	In Effect	SIGNAL	1/11/2022
9900006	TRK06	260	Closed/Completed	CONST	3/29/2023	3/31/2023
9900007	TRK07	260	In Effect	CONST	3/14/2023
9900008	TRK07	260	Closed/Completed	CONST	2/28/2023	3/6/2023
9900009	TRK08	259	Closed/Completed	CONST	3/14/2023	3/21/2023
9900010	TRK08	259	Closed/Completed	CONST	2/28/2023	3/6/2023
9900042	TRK37	176	In Effect	TRACK	6/25/2021
9900043	TRK38	226	In Effect	TRACK	9/15/2014
9900044	TRK39	555	In Effect	TRACK	11/1/2019
9900045	TRK40	231	In Effect	TRACK	10/6/2022
9900046	TRK41	206	In Effect	TRACK	6/22/2021
9900047	TRK41	206	In Effect	SIGNAL	6/15/2016
9900048	TRK42	150	In Effect	TRACK	5/17/2019
9900049	TRK42	150	In Effect	TRACK	2/15/2018
9900050	TRK43	150	In Effect	TRACK	3/9/2016
9900051	TRK44	257	Closed/Completed	TRACK	12/28/2021	10/26/2022
9900052	TRK44	257	Closed/Completed	SIGNAL	10/18/2021	10/26/2022

Thanks in advance for any help you can provide!

[v-shex-msft · Accepted solution]

HI @dwilson91,

It seems like a common multiple aggregate calculate issue. I'd like to suggest you refer to the following blog to add a variable with summarize function to calculate expression on the detail level and use iterator function to summary previous calculation results.

Measure Totals, The Final Word 

Regards,

Xiaoxin Sheng

[dwilson91]

Thanks, Xiaoxin @v-shex-msft 

I appreciate your response and your suggestion.  While that seems to make sense, the problem I have is that it is in the context of OVER TIME. The measures calculate active slow zones on Track ID per month/year.  The slow zone can be open for several months or years, so I want to count that Track ID for each month that it's active.  Because of that, I don't know how to embed this suggestion into my measures over time.  Also, my intention is not to use the table, but to take the measures and use them in a bar chart, so I will not have detailed Track IDs in the visual.  I'm just using it now in this thread to show the calculation I'm trying to achieve.

[dwilson91]

@Greg_Deckler Hi Greg. Do you know how I can get an answer to this?  Did I do something wrong in my post?

[dwilson91]

@amitchandak Any chance you might be able to help me with this?