Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have two tables in my data model that are not linked.
In Table 1 - I have a table that shows committed but not actual sales - i.e. these are sales opportunities that a client has told us we have 'won' but the actual sale has not yet occured - i.e. money has not actully been given to us yet.
Product | Commitment Date | Committed Sales |
Product A | 01/24/2012 | $20 |
Product A | 03/04/2018 | $100 |
Product B | 05/02/2018 | $40 |
Product C | 02/03/2018 | $30 |
Table 2 is a table of all actual sales across the region - regardless of if it shows up in our commited sales table - we can have many products that are sold across multiple years - and this is a table that shows monthly sales by product.
Product | Sale Date | Sales Amount |
Product A | 12/31/2015 | $5 |
Product A | 01/31/2016 | $10 |
Product A | 05/31/2018 | $20 |
Product B | 06/30/2018 | $18 |
Product B | 02/28/2018 | $36 |
Product C | 03/31/2018 | $4 |
Product D | 06/30/2010 | $1 |
Product E | 10/31/208 | $40 |
I want to track the amount of actual sales from Table 2, but only sum those that have occured after the 'Commitment Date' in Table 1 for the corresponding product - and it's ok to have things be double counted - like in the case of Product A in Table 1 - For the commitment date of '01/24/2012' i would have a sum of the first three lines of Table 2, but for the next commitment date in Table 1 for Product A - I would only have the sum of line 3 in Table 2 (since that is the only sale that occured after '3/4/2018').
I have an Actual Sales measure:
Actual Sales = Sum[Sales Amount]
but since my tables are not linked, and cannot be - I'm not sure how to create a calculated column or measure that will give me the right amounts in Table 1.
Secondarily - I'd love to be able to break out the actual sales for every line in Table 1 - so the first line would show sales in 2015, 2016, and 2018, while the second line would show sales in 2018 only (all of which occured only after the 'commitment date' in any given year).
Is this possible to do without creating a relationship between the two tables via a new calculated table or adding calculated columns that would do this?
Ideal result would be a table that does this:
Product | Commitment Date | Committed Sales | Actual Sales |
Product A | 01/24/2012 | $20 | $35 |
Product A | 03/04/2018 | $100 | $20 |
Product B | 05/02/2018 | $40 | $18 |
Product C | 02/03/2018 | $30 | $4 |
and bonus awesome would be a result that does this:
Product | Commitment Date | Committed Sales | CY 2015 Actual Sales | CY 2016 Actual Sales | CY 2017 Actual Sales | CY 2018 Actual Sales |
Product A | 01/24/2012 | $20 | $5 | 10 | 0 | 20 |
Product A | 03/04/2018 | $100 | $0 | 0 | 0 | 20 |
Product B | 05/02/2018 | $40 | $0 | 0 | 0 | 18 |
Product C | 02/03/2018 | $30 | $0 | 0 | 0 | 4 |
Thanks!
Solved! Go to Solution.
Hi,
You may download my solution from this file.
Hope this helps.
I think i'm getting closer - but need help with the date portion now.
So what I decided to do was create a measure as such:
Actual Sales = CALCULATE(Sum([Sales Amount]),
TREATAS(VALUES(Table1[Product]),Table2[Product]))
Which give me a summation of sales amount for every product in Table 1 - but now I don't know how to sum the sales amount values in Table2 only if Table1[Product]= Table2[Product] AND where the corresponding Table1[Commitment Date]<=Table2[Sale Date] for each Product in Table 1.
Hi,
You may download my solution from this file.
Hope this helps.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |