the "duration" of a contract WITHIN a certain year

[Anonymous]

Hi, 

I have two tables.

TableA lists a bunch of Contract Names, each contract's Start Date, End Date and Duration (which is the number of days between the end date and the start date). Contracts duration can extend within or beyond a year (or multiple years). 

TableB has a year column and daily value column. 

I want to have a measure that works in a matrix. The column of the matrix is the contract names. The row of the matrix is the calendar year. The measure will calculate the Total Annual Value (TAV) of a particular contract of the row that falls within the year of the column. TAV is defined as the daily value (in TableB) multiplied by the number of days that falls into a particular year. 

For example, if Contract X starts at 4/1/2018 and ends at 3/31/2019.  That is a total duration of 364 days. There are 275 days of the contract duration falls into 2018 and 89 days in 2019. In Table B, if we find the daily value of 2018 is $10 and daily value of 2019 is $100, then the TAV will return 2,750 for year 2018 and 8,900 for year 2019.

Thanks for help!

SeanPBI

[OwenAuger · Accepted solution]

Hi @Anonymous 

I have uploaded a sample PBIX file here.

First off, to set up the model, I would suggest that you start with a Contracts table structured like this, where Name, Start Date, End Date & Daily Value are in one table (I wasn't sure if you already had Daily Value in the same table as the other contract attributes):

Then you should include a Date table containing Year and any other date-related fields you want to filter by, but not related to the Contracts table.

Once you have the model set up like this, there are two methods I would consider:

1. Use a DAX measure to "simulate" filtering the Contracts & multiply the daily value for each contract by the appropriate number of days:
   Here are two ways I wrote the measure:
   

   TAV (Contracts) = 
   SUMX ( 
       GENERATE (
           ADDCOLUMNS(
               SUMMARIZE ( 
                   Contracts,
                   Contracts[Start Date],
                   Contracts[End Date]
               ),
               "Total Daily Value", CALCULATE ( SUM ( Contracts[Daily Value] ) )
           ),
           INTERSECT (
               DATESBETWEEN ( 'Date'[Date], Contracts[Start Date], Contracts[End Date] ),
               VALUES ( 'Date'[Date] )
           )
       ),
       [Total Daily Value]
   )
   
   TAV (Contracts) 2 = 
   SUMX (
        ADDCOLUMNS(
           SUMMARIZE ( 
               Contracts,
               Contracts[Start Date],
               Contracts[End Date]
           ),
           "Total Daily Value", CALCULATE ( SUM ( Contracts[Daily Value] ) )
       ),
       COUNTROWS (
           INTERSECT (
                   DATESBETWEEN ( 'Date'[Date], Contracts[Start Date], Contracts[End Date] ),
                   VALUES ( 'Date'[Date] )
               )
       ) * [Total Daily Value]
   )

2. Alternatively you create a ContractsDaily table that expands Contracts to one row per day each contract is active. This could either replace your original Contracts table or be used alongside it for specific calculations. Then create a relationship between ContractsDaily[Date] and 'Date'[Date].
   
   In my example I created the ContractsDaily table using DAX (based on approach in this SQLBI article😞
   

   ContractsDaily = 
   SELECTCOLUMNS (
       GENERATE (
           Contracts,
           FILTER (
               ALLNOBLANKROW ( 'Date' ),
               AND (
                   'Date'[Date] >= Contracts[Start Date],
                   'Date'[Date] <= Contracts[End Date]
               )
           )
       ),
       "Date", 'Date'[Date],
       "Contract Name", Contracts[Contract Name],
       "Daily Value", [Daily Value]
   )

   then create a much simpler TAV measure:
   

   TAV (ContractsDaily) = 
   SUM ( ContractsDaily[Daily Value] )

Using either of these approaches, you can produce the sort of visual you are looking for:

Note: In the above I summed values on all dates from Start Date to End Date inclusive. You could tweak the formulas to exclude End Date if that's how the contracts work.

Regards,

Owen

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[OwenAuger · Accepted solution]

Hi @Anonymous 

I have uploaded a sample PBIX file here.

First off, to set up the model, I would suggest that you start with a Contracts table structured like this, where Name, Start Date, End Date & Daily Value are in one table (I wasn't sure if you already had Daily Value in the same table as the other contract attributes):

Then you should include a Date table containing Year and any other date-related fields you want to filter by, but not related to the Contracts table.

Once you have the model set up like this, there are two methods I would consider:

1. Use a DAX measure to "simulate" filtering the Contracts & multiply the daily value for each contract by the appropriate number of days:
   Here are two ways I wrote the measure:
   

   TAV (Contracts) = 
   SUMX ( 
       GENERATE (
           ADDCOLUMNS(
               SUMMARIZE ( 
                   Contracts,
                   Contracts[Start Date],
                   Contracts[End Date]
               ),
               "Total Daily Value", CALCULATE ( SUM ( Contracts[Daily Value] ) )
           ),
           INTERSECT (
               DATESBETWEEN ( 'Date'[Date], Contracts[Start Date], Contracts[End Date] ),
               VALUES ( 'Date'[Date] )
           )
       ),
       [Total Daily Value]
   )
   
   TAV (Contracts) 2 = 
   SUMX (
        ADDCOLUMNS(
           SUMMARIZE ( 
               Contracts,
               Contracts[Start Date],
               Contracts[End Date]
           ),
           "Total Daily Value", CALCULATE ( SUM ( Contracts[Daily Value] ) )
       ),
       COUNTROWS (
           INTERSECT (
                   DATESBETWEEN ( 'Date'[Date], Contracts[Start Date], Contracts[End Date] ),
                   VALUES ( 'Date'[Date] )
               )
       ) * [Total Daily Value]
   )

2. Alternatively you create a ContractsDaily table that expands Contracts to one row per day each contract is active. This could either replace your original Contracts table or be used alongside it for specific calculations. Then create a relationship between ContractsDaily[Date] and 'Date'[Date].
   
   In my example I created the ContractsDaily table using DAX (based on approach in this SQLBI article😞
   

   ContractsDaily = 
   SELECTCOLUMNS (
       GENERATE (
           Contracts,
           FILTER (
               ALLNOBLANKROW ( 'Date' ),
               AND (
                   'Date'[Date] >= Contracts[Start Date],
                   'Date'[Date] <= Contracts[End Date]
               )
           )
       ),
       "Date", 'Date'[Date],
       "Contract Name", Contracts[Contract Name],
       "Daily Value", [Daily Value]
   )

   then create a much simpler TAV measure:
   

   TAV (ContractsDaily) = 
   SUM ( ContractsDaily[Daily Value] )

Using either of these approaches, you can produce the sort of visual you are looking for:

Note: In the above I summed values on all dates from Start Date to End Date inclusive. You could tweak the formulas to exclude End Date if that's how the contracts work.

Regards,

Owen

[Anonymous]

Hi OwenAuger and Ashish Mathur,

First of all, I'd like to thank both of you for your input. I really appreciate your time and efforts to help me out. 

Second, you guys are way advanced in DAX. I am still trying to understand the solution OwenAuger offerred.  I am sure it will work, but I'd like to understand better the logic and the formula behind.  I am reading a DAX book while studying OwenAuger's model. I also downloaded the file Ashish shared and studied it. My data layout is a bit different than the one in his table. I should have explained it better in my original post. 

I thought about this challenge in the past couple of days and came up with some ideas. I uploaded a file to here.

1. the data was set up as OwenAuger suggested: two tables with no relationship to each other. One is the contract table. One is a calendar table. 

2. In my original post, I said there is another table listing the daily value of a contract dependent on the day of the year the contract falls into. I came up with another method to export the data from the original source so that now the total contract value (from start date to end date) and duration of the contract (number of days between start date and end date) is part of the Contract table. The daily value is just the total contract value divided by the duration, regardless of the year the contract falls into.  This simplied the siuation a lot. 

I came up with two solutions, one using the calculated column, which is not the preferred solution, but it inspired me to the other solution, which is a DAX measure. These solutions yielded the same results. They are shown on the two tabs of the file. 

Thank you again for your help.  Let me know your thoughts on my solution.

PBISean

[OwenAuger · Accepted solution]

@Anonymous 

I actually like your "Measure" solution - concise and captures the required logic 🙂

One limitation is that it relies on a contiguous date selection, but practically speaking I expect you will always have a continguous date selection (e.g. one whole month or a sequence of months at a time). My ealier measure allowed for any arbitrary date selection, but that's likely not needed.

Below I have just rewritten your Annual Contract Value measure slightly to avoid repeated calculation of the same expressions, but kept your logic. You may also consider SUMMARIZE-ing the Contract table within the SUMX (similar to my ealier measure), which might improve performance if you have a lot of contracts with the same start and end dates.

Annual Contract Value =
VAR MinDate = [BoY]
VAR MaxDate = [LoY]
RETURN
    SUMX (
        Contract,
        Contract[Daily Value]
            * MAX (
                BLANK (),
                MIN ( Contract[End Date], MaxDate ) - MAX ( Contract[Start Date], MinDate ) + 1
            )
    )

Best regards,

Owen

[Anonymous]

Hi Owen,

Wow, you are really a datanaut. I've learned a lot from your original solution. Thanks so much. 

PBISean