cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
fjjohann Regular Visitor
Regular Visitor

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: DatesBetween

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):Capture.png
  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:Capture.png

     

     

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

 

 

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




3 REPLIES 3
Moderator v-yuezhe-msft
Moderator

Re: DatesBetween

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])
1.PNG

 

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
fjjohann Regular Visitor
Regular Visitor

Re: DatesBetween

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.

 

Super User
Super User

Re: DatesBetween

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):Capture.png
  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:Capture.png

     

     

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

 

 

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!