Summarize Measures in a column

[Anonymous]

Dear all,

I´m having trouble finding a solution to the following:

In my company´s budget, we have "three versions" in the database:

1- Planned: have values for all 12 months

2- Actual: have values "up to date" (let´s assume values until month 5)

3- Forecast: future values to be compared with annual plan and future "actuals".

Problem: in the Forecast version, I need the past months values to be replaced by what actually happened (could have multiple lines, so needs to be a sum).

I tried to make an example below what I need:

Table 1 is what I have. Month 1 to 5 of forecast is not important for me anymore, and needs to be replaced by the sum of "Actual" for each month.

Values	Version	Month
2,50	Actual	1
3,00	Actual	1
1,00	Actual	2
1,50	Actual	3
2,00	Actual	3
2,50	Actual	3
2,50	Actual	4
2,00	Actual	5
3,00	Planned	1
2,00	Planned	2
4,00	Planned	3
3,50	Planned	4
6,00	Planned	5
4,30	Planned	6
2,30	Planned	7
4,00	Planned	8
2,30	Planned	9
2,00	Planned	10
5,00	Planned	11
2,30	Planned	12
3,00	Forecast	1
2,00	Forecast	2
4,00	Forecast	3
3,50	Forecast	4
6,00	Forecast	5
12,00	Forecast	6
13,00	Forecast	7
13,00	Forecast	8
11,00	Forecast	9
14,00	Forecast	10
15,00	Forecast	11
11,00	Forecast	12

 
The table below is what I´m expecting. Notice that the Forecast values of months 1 to 5 are replaced by the sum of each period that already happened, in version "Actual".

Values	Version	Month	Year
2,50	Actual	1	2018
3,00	Actual	1	2018
1,00	Actual	2	2018
1,50	Actual	3	2018
2,00	Actual	3	2018
2,50	Actual	3	2018
2,50	Actual	4	2018
2,00	Actual	5	2018
3,00	Planned	1	2018
...	...	...	...
5,50	Forecast	1	2018
1,00	Forecast	2	2018
6,00	Forecast	3	2018
2,50	Forecast	4	2018
2,00	Forecast	5	2018
12,00	Forecast	6	2018
...	...	...	...

 I do have single measures that work for me, as shown bellow. However, I want to use the Waterfall Chart, and I cannot have different measures. I have to have a single column with different versions, in order to work.

Forecast measure

Forecast = 
CALCULATE(
    SUM('Table1'[Values]);
    FILTER(
        'Table1';
        ('Table1'[Version]; 'Table1'[Month])
            IN { 
                ( "Actual"; 1 ); ( "Actual"; 2 ); ( "Actual"; 3 ); ( "Actual"; 4 ); ( "Actual"; 5 );
                ( "Forecast"; 6 ); ( "Forecast"; 7 ); ( "Forecast"; 8 ); ( "Forecast"; 9 ); ( "Forecast"; 10 ); ( "Forecast"; 11 ); ( "Forecast"; 12 )
            }
    )

Planned measure (similar for Actual)

Planned = 
CALCULATE(
	SUM('Table1'[Values]);
	'Table1'[Version] IN { "Planned" }
)

Thank you for the help!

[MFelix]

Hi @Anonymous,

Try this measure:

Measure =
SWITCH (
    TRUE ();
    CALCULATE ( SUM ( 'Fact'[Values] ); 'Fact'[Version] = "Actual" )
        = BLANK ()
        && MAX ( 'Fact'[Version] ) <> "Planned"; CALCULATE ( SUM ( 'Fact'[Values] ); 'Fact'[Version] = "Forecast" );
    CALCULATE ( SUM ( 'Fact'[Values] ); 'Fact'[Version] = "Actual" )
        <> BLANK ()
        && MAX ( 'Fact'[Version] ) <> "Planned"; CALCULATE ( SUM ( 'Fact'[Values] ); 'Fact'[Version] = "Actual" );
    CALCULATE ( SUM ( 'Fact'[Values] ); 'Fact'[Version] = "Planned" )
)

Should return what you need.

Regards,

MFelix

[Anonymous]

Thank you @MFelix,

I like your approach, and it would solve my problem if I didn´t need to use the "waterfall chart".

That means I need this "forecast", but I need to keep my "Planned" values in a different version, so I´m guessing I cannot use a measure in Waterfall Chart. I need to use a column of values in "Y Axis" with "version" as category.

Is there a way to transform this measure formula into a "calculated column" of values, in which "version" would be set to "forecast" for all months?

Thank you once again,

Eduardo

[Anonymous]

I found a solution modifying my SQL Query. It works, but I still wish I could find the same solution working directly in PowerBI Desktop modelling with DAX.

Below is the SQL Query that worked, so it might help you find the solution in DAX.

[...]
  CASE 
    WHEN MONTH < 6 AND VERSION = 'FORECAST' THEN (SELECT 
                                         SUM(ACTUAL.VALUES) AS "Values"
                                     FROM SAP_CO.ACTUAL
                                     WHERE ACTUAL.COSTCENTER = FORECAST.COSTCENTER 
                                     AND ACTUAL.MONTH = FORECAST.MONTH
                                     AND ACTUAL.YEAR = FORECAST.YEAR
                                     AND ACTUAL.CLASSCOST = FORECAST.CLASSCOST
    ELSE
        FORECAST.VALUES AS "Values",
[...]

Thank you