Actuals for past periods+planned values for future // incorrect totals in measure

[MattStgt]

Hi all!

I need help urgently as I only have few days left to fix that problem - please.

I have a project reporting in power bi and want to show the actuals for all past periods+ planned values for the future (independent from years or fiscal years, so no ytd needed) in one measure please.

The relevant part of my data model looks like this:

I have a fact table with actuals on a daily basis (Total ACT)

another fact table with forecast values on monthly basis (Total BUD)

a datetable that is connected to both (DimDate)

I have already added a measure that is showing me the remaining forecast:

Remaining Forecast = VAR
var1 = [Last ACT Date] RETURN
If(MIN(DimDate[Date])<=var1;
Blank();
CALCULATE(
[Total Budget in €];
'Total BUD'[Date] > var1))

for the periods after the last actuals are in. However it is still unclear how it will behave in the last period if for example 5 days of the next month are booked already but it should show the current month with planned values until the month is over.

Of course I also have an Actuals measure and now trying to combine both. That also works perfectly fine in the table view, so it is showing me the actuals for the past, and the planned values, for the future in the table. However it absolutely doesn't sum up correctly 😕 Can somehelp me where the bug is pleasE?

Forecast at Completion =
VAR FCAC = [Total Actuals in €]+[Remaining Forecast]
VAR TAB1 = Summarize(DimDate;DimDate[Year-Month];"FCAC";FCAC)
Return
If(ISFILTERED(DimDate[Year-Month]);
FCAC;
SUMX(TAB1;[FCAC]))

I have tried to fix that with a proposal to another topic here but it didnt work in my case.

HELP is very much appreciated!! 🙂

[MattStgt]

I've put some testing values here and like you can see the actuals are taken correctly until the last period, and then it takes the budget values from the left column, however it does not sum it up correctly.

[MattStgt]

I have now changed the syntax, so that the actuals are aggregated correctly. However, now I think the issue must be with the remaining forecast measure as this doesnt sum up at all. Can some one help?

This is the corrected syntax now:

Forecast at Completion =
VAR FCAC = [Total Actuals in €]+[Remaining Forecast]
VAR TAB1 = Summarize(Values(DimDate[Date]);DimDate[Date];"FCAC";FCAC)
Return
If(HASONEFILTER(DimDate[Date]);
SUMX(TAB1;[FCAC]);
FCAC)

the remaining forecast measure looks like this:

Remaining Forecast = VAR
var1 = [Last ACT Date] RETURN
If(MIN(DimDate[Date])<=var1;
Blank();
CALCULATE(
[Total Budget in €];
'Total BUD'[Date] > var1))

[MattStgt · Accepted solution]

I finally found the solution, honestly not 100% sure how it works. but maybe it helps someone:

The problem was with the remaining forecast figure. I have adjusted this:

Remaining Forecast = VAR
var1 = [Last ACT Date] Return
CALCULATE (
SUM ( 'Total BUD'[Budget in Euro]);
FILTER (
'Total BUD';
'Total BUD'[Date] > var1
)
)

so my overall measure now works:

Forecast at Completion =
VAR FCAC = [Total Actuals in €]+[Remaining Forecast]
VAR TAB1 = Summarize(Values(DimDate[Date]);DimDate[Date];"FCAC";FCAC)
Return
If(HASONEFILTER(DimDate[Date]);
SUMX(TAB1;[FCAC]);
FCAC)

[Robert1981]

I know this is an older post, but I need help with a similar issue.

I also need to calculate the future values. However in my case, I don't have table columns.

My actuals are calculated with a measure and my future values are not in a table, but need to carry forward the last actual amount in the measure.

E.g. March actual, needs to be the future value for April, May, etc.

[Ashish_Mathur]

Hi,

Share some data, explain the business context and show the expected result.

[Robert1981]

I have the following tables with fields. With the special characters I indicated the relationships:

Table: BPC             Table: Period          Table: Company              Division                  Fin Item

Company code #   Period -                   Company Code #            Division *                Fin Item ^

Period -                  Month                     Company Name              Division name        Fin Item Name

Division *                Date                        Country                                                          Aggregation lvl1

Amount                  Quarter                    Region                                                           Aggregation lvl2

Fin Item ^              Actual Y/N

Nested in the Period table I created a measure for Net Sales:

Net Sales = calculate(sum('BPC'[Amount]),filter('Fin Item','Fin Item'[Agg Lvl 2]="1.1 - Net Sales"))

This measure works for the actual results in the matrix table.

To determine the actual period vs a future period, I added a column in the period table, called "Actual Y/N". It will have a 1 for past periods and a 0 for future periods.

I created a second measure to help populate the future periods:

Sales tot = if(calculate(sum('Period'[Actual Y/N]))>0,'Period'[Net Sales],103000).

Instead of 103000, I want the formula to pick up the Net Sales value from the last actual period.

Period	Actual	Future
Jan	1,560,459
Feb	1,245,320
Mar	1,340,540
Apr		1,340,540
May		1,340,540
Jun		1,340,540
Jul		1,340,540

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[Robert1981]

would love to, but the file has confidential information.

Unless you can help me how I can provide the shell without data.

[Ashish_Mathur]

Hi,

Anonymise the data and share the download link.  Show the expected result on the anonymised dataset.

[Robert1981]

Does this work:

mock report

[Ashish_Mathur]

Hi,

That takes me to a sign-in page.

[Robert1981]

https://gofile.io/?c=dF13Hy

Dit link should be open

[Ashish_Mathur]

Hi,

There are just way too many tables in that file.  I am totally confused about what you want.

[Robert1981]

For what I am looking for, the "Period" table and "BPC IS" are the only important tables.

In the period table is a measure called [sales external]. This measure calculates the net sales out of the table "BPC IS". This measure only works for the data that is actually loaded (past data).

In the table "period" there is a measure called [Sales Tot]. This measure should pick up [External sales] for each period in the past. However for periods in the future, I want it to take the latest past period value.

So in the report, you see that the net sales gets calculated for each period. [sales external] and [sales tot] and the same for past periods. For future periods [sales external] is blank, [sales tot] is showing 103,000. Instead of 103,000 I want to see the value of March 2020: 23,712,648 for all future periods. Of course for every new period this needs to automatically changes to the next latest period.

In this measure I want to display the value

[Robert1981]

https://app.powerbi.com/reportEmbed?reportId=0a572de6-62d5-45e5-aba4-f82e34587497&config=eyJjbHVzdGV...

Does this work better? If not, how can I get the file outside the firewall. I don't have a SP site that is public.

[Robert1981]

I have the following tables with fields. With the special characters I indicated the relationships:

Table: BPC             Table: Period   Table: Company    Division           Fin Item

Company code #   Period -            Company Code #   Division *         Fin Item ^

Period -                  Month              Company Name     Division name  Fin Item Name

Division *                Date                 Country                                           Aggregation lvl1

Amount                  Quarter            Region                                             Aggregation lvl2

Fin Item ^              Actual Y/N

Nested in the Period table I created a measure for Net Sales:

Net Sales = calculate(sum('BPC'[Amount]),filter('Fin Item','Fin Item'[Agg Lvl 2]="1.1 - Net Sales"))

This measure works for the actual results in the matrix table.

To determine the actual period vs a future period, I added a column in the period table, called "Actual Y/N". It will have a 1 for past periods and a 0 for future periods.

I created a second measure to help populate the future periods:

Sales tot = if(calculate(sum('Period'[Actual Y/N]))>0,'Period'[Net Sales],103000).

Instead of 103000, I want the formula to pick up the Net Sales value from the last actual period.

Period	Actual	Future
Jan	1,560,459
Feb	1,245,320
Mar	1,340,540
Apr		1,340,540
May		1,340,540
Jun		1,340,540
Jul		1,340,540