Howto get earnings from multiple contracts in a choosable timeframe

[serialize]

Hi all, I have a nice brainteaser for you, I'm getting crazy with 😫

I have the following table with information regarding closed, running or ongoing contracts, where I always have a startDate and an EndDate and an already calculated column with "EarningsPerDay".

Contract ID	StartDate	EndDate	EarningsPerDay	...
2	10.10.2020	31.12.2022	5,00
3	01.02.2021	15.03.2021	7,50
4	12.03.2021	18.05.2021	2,30
...

Now I would like to see the earnings within a specific timeframe over the contracts, which are active within this timeframe (for example in a barchart assigned to days, months, aso.) and it should be possible to get an overview of all currently active contracts within a timeframe, for example:

Chosen Timeframe => Active Contract IDs

• 13.03.2021 - 15.03.2021 => Contract ID 2,3 and 4 are active
• 15.05.2021 - 30.06.2021 => Contract ID 2 and 4 are active (have active days in this period)

I've already created a Calendar-Table, but I don't know, how to deal with the timeframe between the StartDate and the EndDate. 
I think I need some kind of multidimensional table with the following dimensions

• every day of the chosen timeframe (from -> to)
• contracts
• measure which delivers EarningsPerDay, if the contract is active on this day or 0 (zero), if the contract is not active

At the moment I only have the cumulated EarningsWithinTimeframe, using the following Formula, which seems to work correctly, 
but I didn't get the correct values shown in a barchart with years and months. The barchart shows only the same value in each bar.

EarningsWithinTimeframe = CALCULATE(
    SUMX( 
        VALUES(_Calendar[Date]),
        SUMX(Contract,
            IF(
                Contract[StartDate] <= _Calendar[Date] &&
                Contract[EndDate] >= _Calendar[Date]
                Contract[EarningsPerDay],0
            )
        )
    ),
    ALLSELECTED(_Calendar)
)

The magic riddle: How do I get the correct SUM of earnings shown in a barchart for Years, Months and Days?

I hope, you understood the problem and could help me here,

thank you so much,

best regards,

Stefan

[serialize]

Thank You @Jihwan_Kim this goes in the right direction. The "earnings total measure" are too high in edgecases (earnings starts/ends somewhere within the selected timeframe) because it count's also the days, before earnings start oder after earnings ended. I've made a mixture of your "earnings total measure".

Now the barchart looks good. I still have some issues, which I don't understand. I'll check them in the next days and hopefully come back with a solution or further questions 🙂

Thanks a lot!!!

[Jihwan_Kim]

Hi, @serialize 

Please check the below picture and the sample pbix file's link down below.

Active Contract ID Count Measure =

CALCULATE (

COUNTROWS ( Earnings ),

FILTER (

Earnings,

Earnings[StartDate] <= MAX ( Dates[Date] )

&& Earnings[EndDate] >= MIN ( Dates[Date] )

)

)

Earnings total measure =

SUMX (

FILTER (

Earnings,

Earnings[StartDate] <= MAX ( Dates[Date] )

&& Earnings[EndDate] >= MIN ( Dates[Date] )

),

Earnings[EarningsPerDay] * COUNTROWS ( Dates )

)

https://www.dropbox.com/s/m4ii6m6gn2ldn8d/serialize.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[serialize · Accepted solution]

Hello,

thank you @Jihwan_Kim, with your help I found the following solution, it's a bit a mixture between your solution and mine.

With the following measure, you get the sum of all earnings within the selected timeframe:

Earnings total measure = CALCULATE(
    SUMX(
        VALUES(Dates[Date]),
        SUMX(Earnings,
            IF(
                Earnings[StartDate] <= Dates[Date] &&
                Earnings[EndDate] >= Dates[Date],
                Earnings[EarningsPerDay],0
            )
        )
    )
)

 
And if you would like to get a list with all active contracts in this timeframe, you can use the following measure as filter on a table.
You get all contracts, which match the following cases:

1. contracts, which start before selected timeframe and and after selected timeframe
2. contracts, which start before selected timeframe and end within the selected timeframe
3. contracts, which start in the selected timeframe and end within the selected timeframe
4. contracts, which start within the selected timeframe and end after the selected timeframe

Active Contracts = COUNTROWS(
    FILTER(Earnings,
    Earnings[StartDate] <= MAX(Dates[Date]) &&
    Earnings[EndDate] >= MIN(Dates[Date])
    )
)

Apply this measure as a filter to a table and choose to show all elements, which are not blank.

I hope, this is helpful for other people in the community,

thanks a lot,

Stefan