How to get missing previous quarter data

Question

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

user_2872922 · Answer

Create a new Budget table.

Combine the two tables using JoinKind.FullOuter with all the columns except Revenue as the key
Expand the Budget Revenue column of the resultant table
Fill Down the Budget Revenue column
delete the unneeded columns and re-order the columns

let
    Source = Table.NestedJoin(
                Revenue, {"As of Date", "Property Id", "Quarter"}, 
                Budget, {"As of Date", "Property Id", "Quarter"}, "Budget", 
                JoinKind.FullOuter),
    #"Expanded Budget" = Table.ExpandTableColumn(Source, "Budget", {"Budget Revenue"}, {"Budget Revenue"}),
    #"Filled Down" = Table.FillDown(#"Expanded Budget",{"Budget Revenue"}),
    #"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"YTD Revenue"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"As of Date", "Budget Revenue", "Quarter"})
in
    #"Reordered Columns"

How to get missing previous quarter data

1 Answers