How do I dynamicly use two columns within two dates?

[davidglol]

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!

[Anonymous]

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

[Greg_Deckler]

@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 * __Price

[davidglol]

Hey greg,

Thanks for your input. I will try it later and give u are response 🙂