Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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)
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.
Alternatives:
Cheers,
Owen
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
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.
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)
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.
Alternatives:
Cheers,
Owen