Creating new measure with excisting measures

[ceesl]

Dear all,

trying to get some tips and tricks on how to approach my issue:

Situation:
I use powerBi to show our sales team the forecast we have for their accounts, using 2 different measures: Actuals and forecast (2 different queries.

I want to create an overview how this looks like on a full year. so if we are in the month June, I want the months January/May to be actuals, and June till December Forecast.
So:
1-2-3-4-5-6-7-8-9-10-11-12
A-A-A-A-A-F-F-F-F-F- F- F

What could be a good trick to combine the 2 measures into one, so that my new measure (called YEF) shows both measures, but will change when we enter a new month.

Working with a parameter so i can select which month shows which data?
coming from only Excel, I'm a little bit lost of what is possible with M & Powerbi.

Thank you very much for any input or tips which will show me the right direction.

Cees

 

[horseyride]

Attached

 

[ceesl]

Thank you very much for the idea & input.
I struggle with the format I have, the data is scrambled in different lines, like example below.
the product is unique, the date is changing and the forecast and actuals are in different collumns. that's the result of the different queries I appended.

Product	data	Actuals	forecast
A	1-1-2018	0	12
b	1-1-2018	0	11
c	1-1-2018	0	6
d	1-1-2018	0	4
e	1-1-2018	0	32
A	1-2-2018	0	12
b	1-2-2018	0	11
c	1-2-2018	0	6
d	1-2-2018	0	4
e	1-2-2018	0	32
A	1-12-2017	11	0
b	1-12-2017	34	0
c	1-12-2017	13	0
d	1-12-2017	34	0
e	1-12-2017	24	0

 

[horseyride]

See attached

(1) Convert dates to month numbers
(2) Group on Product and Month
(3) Unpivot to separate Actual and Forecast onto separate row
(4) Create first table that contains Actuals for any month up to specified month
(5) Create second table that contains Forecast for any month after specified month
(6) Combine Tables

Output can be table or pivottable based on query


let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"data", type datetime}, {"Actuals", Int64.Type}, {"forecast", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Month", each Date.Month([data])),
#"Grouped Rows" = Table.Group(#"Added Custom", {"Product", "Month"}, {{"Actual", each List.Sum([Actuals]), type number}, {"Forecast", each List.Sum([forecast]), type number}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Grouped Rows", {"Product", "Month"}, "Attribute", "Value"),
#"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Attribute] = "Actual")),
Actuals = Table.SelectRows(#"Filtered Rows", each [Month] <= GetValue("ActualsThrough")),
#"Filtered Rows2" = Table.SelectRows(#"Unpivoted Columns", each ([Attribute] = "Forecast")),
Forecast= Table.SelectRows(#"Filtered Rows2", each [Month] > GetValue("ActualsThrough")),
Together = Actuals & Forecast
in
Together


and the function named GetValue to read the named cell "ActualsThrough" from excel

(rangeName) =>
Excel.CurrentWorkbook(){[Name=rangeName]}[Content]{0}[Column1]

 

[horseyride]

Updated version

See attached. I believe this does what you need


let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"data", type date}, {"Actuals", Int64.Type}, {"forecast", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Month", each Date.Month([data])),
#"Grouped Rows" = Table.Group(#"Added Custom", {"Product", "Month"}, {{"Actual", each List.Sum([Actuals]), type number}, {"Forecast", each List.Sum([forecast]), type number}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Grouped Rows", {"Product", "Month"}, "Attribute", "Value"),
Actuals = Table.SelectRows(#"Unpivoted Columns", each [Attribute] = "Actual" and [Month] <= GetValue("ActualsThrough")),
Forecast= Table.SelectRows(#"Unpivoted Columns", each [Attribute] = "Forecast" and [Month] > GetValue("ActualsThrough")),
Together = Actuals & Forecast
in
Together

 

[ceesl]

Thank you very much!
this is indeed exactly what I need!

Thank you!!