Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Contezini
Frequent Visitor

Advanced Sum between Dates

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_IDSite_Size
S_00167
S_00233
S_00347
S_00415

 

and Site Log registration:

Site_IDLog ChangeDate
S_001Started01/01/2022
S_001Ended28/02/2022
S_001Started16/05/2022
S_002Started01/02/2022
S_002Ended07/03/2022
S_002Started01/05/2022
S_002Ended31/05/2022
S_003Started01/01/2022
S_003Ended10/03/2022
S_004Started01/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:

Contezini_0-1653984658766.png

 

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

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

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.

2.png

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 
)

3.png

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] )

1.png

View solution in original post

4 REPLIES 4
tamerj1
Super User
Super User

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.

2.png

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 
)

3.png

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] )

1.png

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

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
Super User
Super User

@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"))

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors