Summarising across start and end dates in 2 columns with varying period

[karthikv]

I have data in the following format -

ItemNo	Current State	Previous State	StartDate	EndDate	Amount
1	Active	Yet to Start	1-Apr-18	1-Mar-18	10
2	Active	Active	1-Apr-18	1-Mar-18	20
3	Active	Yet to Start	1-Apr-18	1-Mar-18	30
1	In Progress	Active	1-Mar-18	1-Jun-18	10
2	Active	Active	1-Mar-18	1-Jun-18	20
3	Active	Yet to Start	1-Mar-18	1-Jun-18	30
2	In Progress	Active	1-Jun-18	1-Jul-18	10
3	Active	Yet to Start	1-Jun-18	1-Jul-18	10
1	Active	Yet to Start	1-Jul-18	1-Oct-18	30
2	In Progress	Active	1-Jul-18	1-Oct-18	20
3	Active	Yet to Start	1-Jul-18	1-Oct-18	10

The users will have Start Date and End Date as the filters along with Current State and Present State. The task is to be able to traverse through the periods and obtain the summarized result.

For example, in the above data if we set Start Date as 1-Apr-18 and End Date as 1-Oct-18, Current State - Active, Previous State - Yet To Start, the expected total of 'Amount' should be 120.

With the Current State (Active) and Previous State  (Yet to Start) set, the data would be as shown below -

ItemNo	Current State	Previous State	StartDate	EndDate	Amount
1	Active	Yet to Start	1-Apr-18	1-Mar-18	10
3	Active	Yet to Start	1-Apr-18	1-Mar-18	30
3	Active	Yet to Start	1-Mar-18	1-Jun-18	30
3	Active	Yet to Start	1-Jun-18	1-Jul-18	10
1	Active	Yet to Start	1-Jul-18	1-Oct-18	30
3	Active	Yet to Start	1-Jul-18	1-Oct-18	10

The total aggregated amount is what I am after during the periods Apr to Oct. Start and End Dates across the table would not be in sequence. If user selects say Apr - Jul, then the total amount would be - 10+30+30+10. How should I go about modelling such data?

I did go through the forums that describe multi-period reporting but it is not the similar state. Can someone please guide me on this?

[Phil_Seamark · Accepted solution]

Hi @karthikv

I used two disconnected tables to manage the Start and End date filters, which I can then reference in the following measure.  I've attached a PBIX file for you to have a play with, but I think it's close.

Measure = 
VAR myStartDate = SELECTEDVALUE('Start Date'[Start Date])
VAR myEndDate = SELECTEDVALUE('End Date'[End Date])
VAR Result = 
    CALCULATE(
        
        SUM('Table1'[Amount]) ,
        FILTER(
            'Table1',
            ('Table1'[StartDate] <= myStartDate && 'Table1'[EndDate] > myStartDate) ||  
            ('Table1'[StartDate] >= myStartDate && 'Table1'[StartDate] < myEndDate) 
            )
    )
RETURN Result