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'm trying to formulate a DAX expression to SUM a total from table2's date ranges, based on a date within table1.Here's what Table1 looks like:
TABLE1
CustomerID | SellerID | SaleDate | SaleAmount |
1 | 1 | 1/1/2019 | $100.00 |
1 | 1 | 1/2/2019 | $50.00 |
2 | 1 | 1/1/2019 | $200.00 |
2 | 2 | 3/1/2019 | $20.00 |
and another table that's related (through a dimension) on the CustomerID (but nothing else) with non-overlapping date ranges
TABLE2
CustomerID | CustomerStartDate | CustomerEndDate | CustomerCost |
1 | 1/1/2018 | 2/1/2019 | $20.00 |
1 | 2/1/2019 | 12/1/2019 | $50.00 |
2 | 1/1/2018 | 2/1/2019 | $40.00 |
2 | 2/1/2019 | 12/1/2019 | $30.00 |
I need a measure that is to use the SaleDate from Table1 and find out what rows it falls between in Table2. Once I find those rows from Table2, I need retrieve the SUM of Table2's CustomerCost, so the end result would look like
EXPECTED RESULT
CustomerID | SellerID | SaleDate | SaleAmount | New Cost Measure |
1 | 1 | 1/1/2019 | $100.00 | $20.00 |
1 | 1 | 1/2/2019 | $50.00 | $20.00 |
2 | 1 | 1/1/2019 | $200.00 | $40.00 |
2 | 2 | 3/1/2019 | $20.00 | 30.00 |
I've been fiddling around with the SUMX function, and things seem to work when I have the SaleDate on my table. But as soon as SaleDate is removed, the "New Cost Measure" field goes blank like below:
CURRENT ISSUE
CustomerID | SellerID | SaleAmount | New Cost Measure |
1 | 1 | $150.00 | |
2 | 1 | $200.00 | |
2 | 2 | $20.00 |
Here's the expression for my current (flawed) measure:
New Cost Measure := SUMX( FILTER('Table2', 'Table2'[CustomerStartDate] <= MINX(Table1,[SaleDate]) && 'Table2'[CustomerEndDate] >= MAXX(Table1,[SaleDate]) ), [CustomerCost] )
Would anybody have any ideas how to make this measure work when the SaleDate is removed from the context?
Solved! Go to Solution.
@Anonymous ,
It works as a measure. Expected from you.
From your pbix file.
and without Seller ID.
Just do not summarize SalesAmount.
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Proud to be a Super User!
Hi @Anonymous ,
You were on the right track. Any time you have a many to many relationship, you will need to create a bridge table. In this case it is just the CustomerID. When you build your visual, use that CustomerID (from the new table). This way it will apply to both tables. In the picture think of the filters flowing downhill to both other tables.
Go to Power Query, duplicate one of the tables, get rid of the other columns. Get rid of duplicate rows, and you will end up with only the unique Customer IDs. I just your measure.
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
f
Proud to be a Super User!
Hi @Nathaniel_C
First off, thanks for responding! So I did what you said and modeled things as shown below.
But as you can see, not all is completely solved. You can see how the New Cost Measure is blank for one of the customers if I remove the Seller.
This should be populated with $70 because the sale date ranges fall within the Customer Start & End dates and should be added up ($30 + $40). Any ideas?
If possible please share a sample pbix file after removing sensitive information.
Thanks
Can you create a new column (not measure) like this
Cost Column = sumx( filter(Table2,Table2[CustomerEndDate]>=Table1[SaleDate] && Table1[SaleDate]>=Table2[CustomerStartDate] && Table1[CustomerID]=Table2[CustomerID]),Table2[CustomerCost])
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.
My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
@amitchandak wrote:Can you create a new column (not measure) like this
Cost Column = sumx( filter(Table2,Table2[CustomerEndDate]>=Table1[SaleDate] && Table1[SaleDate]>=Table2[CustomerStartDate] && Table1[CustomerID]=Table2[CustomerID]),Table2[CustomerCost])Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.
My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
So, this technically works but still isn't a measure. Does DAX not allow this type of formulation as a measure..? The reason I ask is because this will make me have to put in a request to the IT group to add a column to their model instead of being able to incorporate this as a measure myself through Power BI.
@Anonymous ,
It works as a measure. Expected from you.
From your pbix file.
and without Seller ID.
Just do not summarize SalesAmount.
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Proud to be a Super User!
From your new expected with customer name from your pbix file.
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Proud to be a Super User!
Please check -https://www.dropbox.com/s/z5934qq5ntjw7pi/CustomerSample.pbix?dl=0
You should able to create a new column from the visualization layer. Unless that is not as per policy.
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 |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |