Calculate variance between actual and target On or Before a target date

[candypox]

Hello community,

I am a beginner in power bi, and I would like your help to figure out my problem.

I have two tables linked by id:

• the first table is the id, target date, and target value
• the second table is the id, actual date, and actual value

I want to calculate the variance between the target and actual according to the target date and actual date.

here is my table:

for example for id 1: I want to know the variance between the sum of actual values that happened on/before the target date:

So before 30-march I have the three orange values, so the result will sum(50,3000,2000)- 2000

My result will be:

Is there any way to implement this on power bi?

Thank you for your help.

 

[alansidman]

With Power Query, create three queries.

Load Table1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"target date", type date}, {"target", Int64.Type}}),
    #"Extracted Month" = Table.TransformColumns(#"Changed Type",{{"target date", Date.Month, Int64.Type}})
in
    #"Extracted Month"

Load Table 2

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Actual Date", type date}, {"Actual", Int64.Type}}),
    #"Extracted Month" = Table.TransformColumns(#"Changed Type",{{"Actual Date", Date.Month, Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Extracted Month", {"Actual Date"}, {{"Total", each List.Sum([Actual]), type nullable number}})
in
    #"Grouped Rows"

Join (Merge) the two tables.

let
    Source = Table.NestedJoin(Target, {"target date"}, Actual, {"Actual Date"}, "Actual", JoinKind.FullOuter),
    #"Expanded Actual" = Table.ExpandTableColumn(Source, "Actual", {"Total"}, {"Actual.Total"}),
    #"Filled Down" = Table.FillDown(#"Expanded Actual",{"target date"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"target date"}, {{"Sum Target", each List.Sum([target]), type nullable number}, {"Sum Actual", each List.Sum([Actual.Total]), type nullable number}}),
    #"Inserted Subtraction" = Table.AddColumn(#"Grouped Rows", "Subtraction", each [Sum Actual] - [Sum Target], type number),
    #"Renamed Columns" = Table.RenameColumns(#"Inserted Subtraction",{{"Subtraction", "Variance"}, {"target date", "target Month"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Sum Target", "Sum Actual"})
in
    #"Removed Columns"