Reply
Berl21
Helper III
Helper III
Partially syndicated - Outbound

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

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!

 

 

4 REPLIES 4
danextian
Super User
Super User

Syndicated - Outbound

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.





Dane Belarmino | Microsoft MVP | Proud to be a Super User!

Did I answer your question? Mark my post as a solution!


"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

Syndicated - Outbound

Berl21_0-1660502949272.png

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!

Syndicated - Outbound

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





Dane Belarmino | Microsoft MVP | Proud to be a Super User!

Did I answer your question? Mark my post as a solution!


"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

Syndicated - Outbound

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
avatar user

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

Check out the March 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)