Forum Discussion

fjjohann's avatar
fjjohann
Frequent Visitor
9 years ago
Solved

DatesBetween

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...
Need Help
  • OwenAuger's avatar
    OwenAuger
    9 years ago

    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

     

     

     

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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's avatar
    fjjohann
    Frequent Visitor
    9 years ago

    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's avatar
      OwenAuger
      Super User
      9 years ago

      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