Advanced Sum between Dates

[Contezini]

Dear community,

I know there are hundreds of topics regarding calculation of Sum Between dates, but none of them seems to fit my needs.

I have 2 data tables + 1 calendar table

Site Sizes:

Site_ID	Site_Size
S_001	67
S_002	33
S_003	47
S_004	15

and Site Log registration:

Site_ID	Log Change	Date
S_001	Started	01/01/2022
S_001	Ended	28/02/2022
S_001	Started	16/05/2022
S_002	Started	01/02/2022
S_002	Ended	07/03/2022
S_002	Started	01/05/2022
S_002	Ended	31/05/2022
S_003	Started	01/01/2022
S_003	Ended	10/03/2022
S_004	Started	01/01/2022

With this data, I want to dinamically know the Total Active Size per Date.

The result should be a measure that allows me creating visualizations such as follows:

In my real dataset, I have ~2000 Site Ids, some of them with +10 Log changes.

I believe there should be an easy, but maybe non-intuitive solution...

[tamerj1 · Accepted solution]

Hi @Contezini 

Here is a sample file with the solution https://www.dropbox.com/t/BFyYg068Dfa9YNbe
We have two issue to solve here.

The first one we have define which start belongs to which end as there is no stage or ranking column that related each start to its relevant end. 

The 2nd issue is generate only the relevant rows in between each start and stop. 

Here is the data model with inactive or no relationship with the date table.

The first issue can be solved by creating the following calculated column

Ranking = 
RANKX ( 
    CALCULATETABLE ( 
        'Site Log', 
        ALLEXCEPT ( 'Site Log', 'Site Log'[Site_ID],'Site Log'[Log Change] ) 
    ), 
    'Site Log'[Date],, 
    ASC 
)

The measure that generates the relevant rows with the relevant site size

Filter Measure = 
VAR CurrentDate = MAX ( 'Date'[Date] )
VAR T1 =
    ADDCOLUMNS (
        SUMMARIZE ( 'Site Log', 'Site Log'[Site_ID],'Site Log'[Ranking],'Site Sizes'[Site_Size] ),
        "@Start", CALCULATE ( MAX ( 'Site Log'[Date] ), 'Site Log'[Log Change] = "Started" ),
        "@End", COALESCE ( CALCULATE ( MAX ( 'Site Log'[Date] ), 'Site Log'[Log Change] = "Ended" ), TODAY ( ) )
    )
VAR T2 =
    GENERATE ( 
        T1,
        VAR StartDate = [@Start]
        VAR EndDate =  [@End]
        RETURN
            CALENDAR ( StartDate, EndDate )
    )
VAR T3 = FILTER ( T2, [@Start] <= CurrentDate && [@End] >= CurrentDate )
RETURN
    MAXX ( T3, [Site_Size] )

[Contezini]

Hi Tamerj1,

thank you for your detailed solution! I have read it and tried out from the moment you posted.

The solution worked for small amount of data, but has a clear performance issue.

My real datatable has too many data for this measure so that it takes too long to load (after 5min waiting for the visualization to load, I gave up waiting).

I saw a very big performance issue when using this only as measure, and considered setting T2 as a calculated Table. I believe this should enhance the performance. Though I need the data to be dynamic (i.e., update when other filters are set) and I'm not sure if my solution will work....

Since I still haven't had time to finish this adaptations and check the results, I didn't accept your answer as solution yet (I think it was automatically set as solution). But I except to be able to check it in the coming days!

For now, thanks for your engagement! I am looking forward to get back to this soon

[tamerj1]

Hi @Contezini 
The issue is that the visual will consume a huge amount of time to load such huge number of sites. But even when it completes loading the chart will not be readable at all! You need in all cases to provide some kind of filter to limit the number of sites involved in one chart. The calculated table will be faster for sure but wil still be relatively slow if too many sites are involved. Also it cannot be dynamic.

[amitchandak]

@Contezini , merge size and log tables

or create a measure

M1 = sumx(relatedtable(Size), Size(Size)

Then try a measure like

calculate( [M1], filter(all(Date), Date[Date] <= Max(Date[Date])), filter(Log, Log[Log Change] ="Started"))
- calculate( [M1], filter(all(Date), Date[Date] <= Max(Date[Date])), filter(Log, Log[Log Change] ="Ended"))