Forum Discussion
How do I dynamicly use two columns within two dates?
Hi Power BI Forum,
I have an issue which I just can't solve. I don't know if I need some kind of transformation of the data first, or anyone have tried solve this kind of problem.
First to understand the business. Food company that gets new price (Catelog) for every month. The prices can change in different specific times, and there are three different price groups the different caffeterias can be in. (Lets say, that we have some different contracts, within different customers) Some can buy it cheaper than others.
Here a example:
I have table Change Log (where I see when what Center have changed in price groups) call it dbo.changelog
| Time Registration | Old Value | New Value | Center |
| 29-10-2021 11:22:00 PM | BLANK | G1 | 1 |
| 30-10-2021 11:55:00 PM | G1 | G2 | 1 |
| 30-10-2021 11:44:00 PM | G3 | G2 | 2 |
| 31-10-2021 11:55:00 PM | G3 | G2 | 3 |
I have a table with all the price levels of each product and how they change over time within each Item Group. dbo.pricelevels
| Start Date | End Date | Price | Product | Item Group | Item Number |
| 29-10-2021 11:44:00 PM | 30-10-2021 11:55:32 PM | 23 | Ice Cream | G1 | 123 |
| 29-10-2021 11:44:00 PM | 30-10-2021 11:55:32 PM | 24 | Ice Cream | G2 | 123 |
| 29-10-2021 11:44:00 PM | 30-10-2021 11:55:32 PM | 25 | Ice Cream | G3 | 123 |
Then, I have another table that shows what the centers have purchased over time dbo.CenterPurchase
| Entry No | Center | Purchase Date | Item Number | Quantity |
| 1 | 1 | 29-10-2021 11:46:32 | 123 | 1 |
| 2 | 1 | 30-10-2021 11:55:32 | 123 | 1 |
| 3 | 2 | 30-10-2021 11:46:32 | 123 | 1 |
| 4 | 3 | 30-10-2021 11:55:32 | 123 | 1 |
Then Lasty, I have a table that shows what centers have one item number in what Item Group at the given moment.
| Center | Item Number | Item Group |
| 1 | 123 | G1 |
| 2 | 123 | G2 |
| 3 | 123 | G3 |
The Idea is that I want to find the Total revenue during a specific time for a speific item group within a specific Center.
Lets say for example Center 1 bought one Ice Cream at Item Number 123, we know within 29 to 30 october, they change price group two times. from blank to G1 and then G1 to G2. There are three price levels, that determine what price group they are in, but the prices can be different depending in what Center they are in and what price group they had at the moment (that also includes when they bought the product)
My Destined table
| Entry No | Center | Purchase Date | Item Number | Quantity | Total Revenue |
| 1 | 1 | 29-10-2021 11:46:32 | 123 | 1 | 1*23 (23 Because center 1 was GP1 at the given moment) |
| 2 | 1 | 30-10-2021 11:55:32 | 123 | 1 | 1*24 (24 because center 1 was GP2 at the given moment looking at changelog) |
| 3 | 2 | 30-10-2021 11:46:32 | 123 | 1 | |
| 4 | 3 | 30-10-2021 11:55:32 | 123 | 1 |
So the idea is to find the specific center, when they bought it, to what price group and what price level. Giving it should be dynamic and the centers can change price group all time between G1-G3.
Anyone have any experience in building such a complex model in Power BI?
Any help would be appericated!
3 Replies
davidglol Try:
Total Revenue column = VAR __Center = [Center] VAR __PurchaseDate = [Purchase Date] VAR __Item = [Item Number] VAR __Quantity = [Quantity] VAR __GroupDate = MAXX(FILTER('dbo.changelog',[Time Registration] < __PurchaseDate && [ValueCenter]=__Center),[Time Registration]) VAR __Group = MAXX(FILTER('dbo.changelog',[Time Registration] = __GroupDate && [ValueCenter]=__Center),[New Value]) VAR __Price = MAXX(FILTER('dbo.pricelevels',[Item Number] = __Item && [Item Group] = __Group && [Start Date] >= __PurchaseDate && [End Date] <= __PurchaseDate),[Price] RETURN __Quantity * __PriceHey greg,
Thanks for your input. I will try it later and give u are response 🙂
Hi davidglol ,
Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or if you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.Best Regards,
Eyelyn Qin
