How to calculate price values with a separate table- date condition

[Berl21]

Hi,

I have a table with vouchers and their respective prices on the one hand and a table with a list of products on the other hand. The second table is built with a column containing the voucher names. Now, the difficulty is that we apply a different logic to prices according to the date the voucher was produced (prices only apply after a specific date).

In Excel I can fix this relatively easily by doing a look up with a condition based on the time column. But in PBI, as a newbie, I don't see how to model this to obtain the column with the correct prices.

I tried to:

- merge the tables and then add a column that filters out everything before the specific date

- make a relationship based on voucher names, then applying a date filter for everything that is before the said date

- add the voucher table as a separate table, then add the filter dimension

None of this worked so far, I either get the wrong prices including former time frames or no result at all.  I am sure there is an easy fix to this, but can't think of how to solve it as of now.

Would love to have some input on this.

Thanks!

[danextian]

Hi @Berl21 

Please post a sample data that we can easily manipulate as well as your expected result.  You can post a link to an excel file.

[Berl21]

Hi @danextian,

I get an error message when trying to post an excel. Hope this screenshot helps? I have simplified the issue by taking out the voucher columns and solely relying on request numbers.

I need to rebuilt column 3 of table 2 in PBI based on the request numbers and the condition "created date"> 08.10.2021. The sum should be 792 € instead of 1386€. How would you approach this?

Many thanks!

[danextian]

You can just  post a link to an excel file in a cloud storage (Google Drive/OneDrive/Dropbox, etc.)

[Ashish_Mathur]

Hi,

share the link from where i can download the PBI file.