Creating a data model which changes the join condition between two tables based on filters

[HimanshuSingh]

Hello All

Problem: The user can select two period, say 202307 (comparative period) and 202310 (current period), I need to compute for that between the two periods:

List Price Band growth: [ List Price of Current Quantity (LP: 150, Current Quantity 7, Period 202310) minus Comparative Period List Price Current Quantity (LP: 140, Current Quantity 7, Period: 202307 ] X Current Period Quantity (Period 202310, Quantity: 7)  The KPI needs to be calculated for each customer and then aggregated.
Data Structure:
I have a table with active subscriptions and List Prices. Data structure of Active_Subscriptions is such that it will contain a row for each period if it is active (and even if no changes have been made to the subscriptions (period between 202307 to 202309)):

YYYYMM	Customer	Product	Quantity	Net Price
202307	A	B	5	100
202308	A	B	5	100
202309	A	B	5	100
202310	A	B	7	120

The list price table is also similar which contains the list price for each month and quantity:

YYYYMM	Product	Quantity	List Price
202307	B	5	120
202307	B	7	140
202308	B	5	120
202308	B	7	140
202309	B	5	130
202309	B	7	150
202310	B	5	130
202310	B	7	150

Solutions explored so far: The easiest solution which I have in my mind is to use a direct query mode and create parameters which will change the Current Period and Comparative Period values and pass it in the SQL query from where I am pulling the tables. The problem is that it will be very time consuming. The data has around 10 million records.

Does the community think there is another way to do it?

Thanks

[MFelix]

Hi @HimanshuSingh ,

Create a dimension table for periods and related both tables based on that table with one to many relationship this will allow for the filtering and comparision.

[HimanshuSingh]

The problem is not filtering the correct months. I want the user to select the current months and comparative months and the computation changes as per the selection

If I were to make my KPI in excel it would merge the two rows in the subscription table and lookup the list prices from the selected months like this:

A	B	C	D	E	F	G	H	I	J	K	L
Reporting Month	Customer	Product	Current Period	Comparative Period	Current Quantity	Comparative Quantity	Current List Price Current Quantity (Based on Current Quantity=7 and Period=202310)	Compartive List Price Current Quantity (Based on Current Quantity=7 and Period=202310)	Current List Price Based on Comparative Quantity (Comparative Quantity=5 and Period = 202307)	Comparative List Price Based on Comparative Quantity (Comparative Quantity=5 and Period = 202307)	List Price Band Changes (H-I) x F
202310	A	B	202310	202307	7	5	150	140	130	120	70

Do you think this will be possible in Power BI?

[MFelix · Accepted solution]

HI @HimanshuSingh ,

For this you need to create a disconnected table for the filtering of the date period you want to compare and then making a measure that allows to get that specific value for the specific time.

Something similar to:

Previous Period = CALCULATE (SUM(Table[Column]), Table[Date] = SELECTEDVALUE(DisconnetedTable[Date]))

Depending on the calculations you need you can then use this measure to make differences and variations and cumulatives.