DatesBetween

[fjjohann]

HI!

I've 2 Tables

TABLE 1:

Column 1: Name

Column 2: Percent

Column 3: Start Date

Column 4: End Date

TABLE 2

Column 1: Name

Column 2: Value

-----

I need a calculation for the value (table 2) * Percent (table 1) dependent on the date:

Example:

I have a visual with monts, and I want to show the value in monthes according to the percent on the date.

[v-yuezhe-msft]

Hi @fjjohann,

When you create a new column in Table 1 using the following formula and create a table visual as follows, does it return your desired result? If not , please share us sample data of above two tables and post expected result here.

Column = Table1[Percent]*RELATED(Table2[Value])

Thanks,
Lydia Zhang

[fjjohann]

Thanks, But the solution is insuficient.

look:

Table 1:

https://drive.google.com/file/d/0Bz1oltp2ew1YNFpHRWtMNzUwMDA/view?usp=sharing

Table 2:

https://drive.google.com/file/d/0Bz1oltp2ew1YM2NxdTVaWWdWcUU/view?usp=sharing

----

Table 1: In this table the percentages of owners' participation in their respective companies are recorded. The percentages change over time, so there are start and end dates for each ownership stake in a company.

In table 1, there is 5 columns:

column 1: Company

column 2: Name (Owners)

column 3: %

column 4: start date

column 5: end date

In table 2: In this table are recorded the expenses of each company, these expenses have a date.

column 1: Company

column 7: Value

column 10: date

I need a measure that calculates between the value of the expense (table2) and the percentage (table1), but it is necessary to take into account the date of the expense with the start and end dates of the participation of the owner of the company.

For example:

Table 1 shows the following:

Line 1: Company: Microsoft Owner: Bill Participation: 50% Start Date: 01/01/2017 End Date: 1/31/2017

Line 2 Company: Microsoft Owner: Bill Participation: 80% Start Date: 2/1/2017 End Date: 2/28/2017

And Table 2 has recorded:

Line 1: Company: Microsoft Value: 100.00 Date: 10/01/2017

Line 2: Company: Microsoft Value: 200.00 Date: 02/10/2017

---

So I need a measure that does the following calculation:

The expense 1 must be paid by the owner Bill at 50.00 because the expense 1 is from 10/01/2017, and referring to table 1, from 01/01/2017 until 01/31/2017 he is a partner at 50% Of the company Microsoft.

Expense 2 must be paid by the owner Bill at 160.00 because the expense 2 is from 10/02/2017, and referring to table 2, from 01/02/2017 until 02/28/2017 he is a member at 80% Of the company Microsoft.

[OwenAuger · Accepted solution]

Hi @fjjohann

Here is a suggestion for how to set up the data model to handle this with DAX.

PBIX sample here (used your Bill/Microsoft example and added Steve)

1. Set up the data model like this (bi-directional relationship between Ownership/Company not strictly required for this measure but may be useful for other purposes):
2. Create this measure to calculate Expense Weighted by Ownership:
   

   Expense Weighted by Ownership = 
   SUMX (
       GENERATE (
           ADDCOLUMNS (
               SUMMARIZE (
                   Ownership,
                   Company[Company],
                   Ownership[Start Date],
                   Ownership[End Date]
               ),
               "ParticipationTotal", CALCULATE ( SUM ( Ownership[Participation] ) )
           ),
           INTERSECT (
               DATESBETWEEN ( 'Date'[Date], Ownership[Start Date], Ownership[End Date] ),
               VALUES ( Expense[Date] ) // This could also be VALUES ( 'Date'[Date] )
           )
       ),
       [ParticipationTotal] * CALCULATE ( SUM ( Expense[Value] ) )
   )

   I am drawing on 'events in progress' patterns from Gerhard Brueckl and SQLBI.
   The GENERATE part of the measure creates a table of Company/Date/Participation combinations, then SUMX sums Participation * Expense for each of those combinations.
3. Then any visual filtered by Ownership[Owner], Company[Company] and the Date table will show the Weighted Expense:

Alternatives:

• You could modify your Ownership table so that the Start/End dates are instead converted to a single Date column covering the date range of ownership, something like here: https://www.sqlbi.com/articles/analyzing-events-with-a-duration-in-dax/
• Or maybe you could pre-process the entire allocation of Expenses to Owner/Company combinations in the data load through the Query Editor.

Cheers,

Owen