Previous Quarter

[nithinsakhamuri]

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).

[Anonymous]

Hi @nithinsakhamuri ,

The solution suggested will take the latest last quarter as Budget.

[Anonymous · Accepted solution]

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
Result

 4. 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