Forum Discussion
Creating a data model which changes the join condition between two tables based on filters
HI Anonymous ,
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.
Hi Anonymous ,
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.
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?
HI Anonymous ,
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.