cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
serialize
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 IDStartDateEndDateEarningsPerDay...
210.10.202031.12.20225,00 
301.02.202115.03.20217,50 
412.03.202118.05.20212,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

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

View solution in original post

3 REPLIES 3
serialize
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!!!

Jihwan_Kim
Community Champion
Community Champion

Hi, @serialize 

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

 

Picture2.png

 

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.


Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

 

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

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

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

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates