Forum Discussion
Previous Quarter
Dear Team, need some help.
I'm having two different tables Finance and Budgets. There is a relationship between two tables.
Finance Table:
| As of Date | Property Id | YTD Revenue | Quarter |
| 3/31/21 | 1 | $5,000 | 1 |
| 6/30/21 | 1 | $6,000 | 2 |
| 3/31/21 | 2 | $7,000 | 1 |
| 6/30/21 | 2 | $8,000 | 2 |
Budgets:
| As of Date | Property Id | Budget Revenue | Quarter |
| 3/31/21 | 1 | $10,000 | 1 |
| 6/30/21 | 1 | $10,000 | 2 |
| 3/31/21 | 2 | $11,000 | 1 |
The business doesn't want to enter the data if the Budget Revenue is same as the last quarter.
There is a quarter slicer on the page and I'm using Finance[Quarter]. Let's say I'm selecting 2nd quarter and there is no quarter 2 data for the property id 2 on the Budgets table and in this case we have to show Budget Revenue from last quarter i.e 3/31/2021($11,000).
hi nithinsakhamuri ,
Developed a solution as follows
1. Created a column QtrNumber = Yearfrom (AsofDate) *100 + Quarter in both YTD Revenue and Budget Table.
This is created to ensure you select the quarter using this column. Otherwise when the year changes there will be issues.
2. The 2 tbales are not connected.
3. Create a measure
Budget = var _id = Selectedvalue('YTD Revenue'[Property Id]) var _qtrNumber = SELECTEDVALUE('YTD Revenue'[QtrNumber]) var _budQtr = CALCULATE(MAX(Budget[QtrNumber]), Budget[QtrNumber] <= _qtrNumber && Budget[Property Id] = _id) // Get Max QtrNumber from Budget table for the propertyid var Result = CALCULATE(MAX(Budget[Budget Revenue]), Budget[QtrNumber] = _budQtr && Budget[Property Id] = _id) //Get the corresponding Budget Return Result4. Create a slicer using YTD Revenue - QtrNumber
5. Create a Table visual with values as AsofDate, PropertyID, Quarter, Revenue, Budget ( measure created above)
Check it out.
If it works please accpet it as Solution and also give Kudos.
Cheers
CheenuSing
2 Replies
hi nithinsakhamuri ,
Developed a solution as follows
1. Created a column QtrNumber = Yearfrom (AsofDate) *100 + Quarter in both YTD Revenue and Budget Table.
This is created to ensure you select the quarter using this column. Otherwise when the year changes there will be issues.
2. The 2 tbales are not connected.
3. Create a measure
Budget = var _id = Selectedvalue('YTD Revenue'[Property Id]) var _qtrNumber = SELECTEDVALUE('YTD Revenue'[QtrNumber]) var _budQtr = CALCULATE(MAX(Budget[QtrNumber]), Budget[QtrNumber] <= _qtrNumber && Budget[Property Id] = _id) // Get Max QtrNumber from Budget table for the propertyid var Result = CALCULATE(MAX(Budget[Budget Revenue]), Budget[QtrNumber] = _budQtr && Budget[Property Id] = _id) //Get the corresponding Budget Return Result4. Create a slicer using YTD Revenue - QtrNumber
5. Create a Table visual with values as AsofDate, PropertyID, Quarter, Revenue, Budget ( measure created above)
Check it out.
If it works please accpet it as Solution and also give Kudos.
Cheers
CheenuSing