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

candypox

New Member
Joined
Nov 14, 2022
Messages
1
Office Version
  1. 2019
Platform
  1. Windows
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:

unkCandy_0-1668419721211.png


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:

unkCandy_1-1668419936774.png


Is there any way to implement this on power bi?

Thank you for your help.
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
With Power Query, create three queries.

Load Table1
Power Query:
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
Power Query:
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.
Power Query:
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"
 
Upvote 0

Forum statistics

Threads
1,223,992
Messages
6,175,822
Members
452,672
Latest member
missbanana

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top