Calculation Min Date in the Sum expression

[AndreyShepel91]

Hi Experts!

I've just faced with the problem of the following calculation:

I the sample data model i have two non-linked table (it's the idea of this task and they shouldn't be related).

First table:

MainDate

Sales

Second Table:

FXDate

FXRate

In the pivot table i have the calculated WeekStart dimension based on the MainDate and i am calculating the Rates on those WeekStart days using the following expression:

=sum(IF(MainDate=FXDate, FXRate))

The problem is, that this expression is calculating the sum of 7 records from the FXRate table, but i need to take only the min date of each week.

=sum(IF(Min(MainDate)=FXDate, FXRate)) does not work, as it uses the nested min function, which should be aggregared.

The expected result - to fetch the FXRates per each WeekStart dates via the set analysis update.

Would be grateful for each idea!
Thanks.

[hallquist_nate]

I think I understand what you are trying to accomplish.  Because the dates to actually relate, you don't want to link the tables.  Makes sense.  I think you have two options here.

1.  Use a Calendar table and link the dates from each table to the calendar.  With a Calendar table, you can create a Weekstart() field, and by keying your Main Date and FX Date to the Calendar, your dates will bee associated appropriately.

2.  Concatenate the two tables and label the date fields the same name, so you can select values accordingly.  

Personally, I would try and make the calendar work.  I like Calendar tables, because they are very robust.  However, for the sake of simplicity, I have concatenated your two tables together and uploaded the app.  Take a look and see if this meets your needs.  

Nate

[sunny_talwar]

Is this what you are looking to get?

[AndreyShepel91]

Thanks, Sunny!
Your solution works perfectly, but it's still applicable when changing the field value in the script.

I am looking for some other solution with the set analysis update only, meaning data mode/script logic stay the same.

Thanks for your feedback!

[AndreyShepel91]

Thank you for your advice!
I agree with you on the advantages of using Calendar tables, but this is the really specific cases which needs no changes in the data model.

I am trying to solve the issue using the set analysis only.

[sunny_talwar]

If you don't want your expression to change based on selection, you can try this

=Sum({1} If(WeekStartMainDate = FXDate, FXRate))

 

[Brett_Bleess]

Andrey, did Sunny's last post get you what you wanted?  If so, please be sure to use the Accept as Solution button on that post to give him credit for the assist and let everyone else know that worked for you.  If you did something else, consider posting that and then mark that as the solution, and if you are still working on things, leave an update for everyone.

Regards,
Brett