Measure to Sum Value based on Date Ranges in One Table Given Date From Another

[Anonymous]

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?

[Nathaniel_C]

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

[Anonymous]

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?

[amitchandak]

If possible please share a sample pbix file after removing sensitive information.
Thanks

[Anonymous]

Not sure what the best way to share files is, but I used gofile here:

https://gofile.io/?c=OrDBr9

[amitchandak]

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

[Anonymous]

---

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

[Nathaniel_C · Accepted 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

[Nathaniel_C]

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

[amitchandak]

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.