cancel
Showing results for
Did you mean:
Frequent Visitor

## Howto get earnings from multiple contracts in a choosable timeframe

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
1 ACCEPTED SOLUTION
Frequent Visitor

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

3 REPLIES 3
Frequent Visitor

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

Community Champion

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

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.

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.

Best regards, JiHwan Kim

Frequent Visitor

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

Announcements