4 Replies Latest reply on Aug 22, 2018 2:29 AM by jekaterina tint

Sum of measure from second data source between dates from first source

Hi everyone!

I have two tables from Excel. One with sales data and the other one with promotion data (incl promotion start and end date).

I am trying to create a measure with the sum of the sales within the promotion period (Measure: Vol for period). Following this thread (calculating sum of a measure between two dates without "between" Operator??)  I have created an IF-THEN measure, but currently, it gives the result as null and I cannot figure out why.

The calculated measure not giving the result is=

IF ATTR([sales (data)].[Date]) >= MIN([Start date])

AND ATTR([sales (data)].[Date]) <= MAX([End date])

THEN SUM([sales (data)].[Volume])

END

Any help would be highly appreciated. Thanks!

• 1. Re: Sum of measure from second data source between dates from first source

Jekaterina,

Not sure if I got there, but maybe the below can give ideas.

I think there may a few issues at hand.

1. I think it may be better to make the sales datasource the primary

and the promo the secondary. I think that was partially causing the null

you were seeing because the ATTR() was returning an asterisk.

2. You may need to pull some fields from both sources onto the detail shelf

to establish the relationship between the two. Will to see how that works

3. Will need to check the table calculation settings when totaling the volume.

• 2. Re: Sum of measure from second data source between dates from first source

Thank you!

Would you mind sharing this via Public?

Based on your advice I got the calculation to show for specific dates, but could not yet get the totaling/aggregation to work.

• 3. Re: Sum of measure from second data source between dates from first source

Jekaterina,

Apololgies, I think I got the version wrong,

not sure if you were able to open it from the Forum Thread.

Here it is on Public:

• 4. Re: Sum of measure from second data source between dates from first source

Thank you!