Report showing realised values and estimate for rest of year

[sdjensen]

Hi,

I need to create a report that will show me realised values and estimated values for the rest of the year.

The report should be a table having finance accounts on rows and months in the columns. If the user filter the year to 2017 and month to april the first 4 columns should show realised values (one measure) for Jan-Apr and the columns for May-Dec should then show estimated values (another measure) and finally there should be a column with a sum of all columns to show an full year estimate.

I am thinking that I somehow create a new measure that based on the selected period would show either the realised measure or estimated measure in the column.

Does any of you brilliant minds have an idea on how to create this?

[Sean]

@sdjensenPerhaps something like this...

1) Create a COLUMN in your Calendar Table

Full Month =
IF (
    TODAY () >= EOMONTH ( 'Calendar'[Date], 0 ),
    "Full Month",
    "Incomplete Month"
)

 2) And then something like this MEASURE

Measure =
IF (
    MIN ( 'Calendar'[Full Month] ) = "Full Month",
    [MEASURE for Actual], // or SUM ( Table[Actual] )
    [MEASURE for Estimate] // or SUM ( Table[Estimate] )
)

[sdjensen]

@Sean : I tried 2 different approaches that both give me the same result (problem) - I get the right result for each month, but the total either show me the sum of the actual amount or the estimated amount depending of if I use Min or Max in my formular.

First Test (I first tried without creating the calculated column):

Test = 
IF(
	MONTH( MIN( 'Date'[Date] ) ) < MONTH( TODAY() );
	[Actual];
	[Budget/Estimate]
)

Second Test (I created the calculated column and the measure):

Actual/Estimate = (Column)
IF( YEAR( TODAY() ) = YEAR( 'Date'[Date] );
    IF(
        TODAY() >= EOMONTH( 'Date'[Date]; 0 );
        "Actual";
        "Budget"
    );
    BLANK()
)

Test2 = (Measure)
IF(
	MIN( 'Date'[Actual/Estimate] ) = "Actual";
	[Actual];
	[Budget/Estimate]
)

As you can see in the attached photo the Total Show the Actual Total and not the sum of the measure values.

Wonder if I could create a new column in my fact tables to filter the rows?

[sdjensen]

Calculating the wanted values in the fact table was the solution to my problem for before. The only problem with this solution is that the user don't have any control. The report will always show the actual from start of year to previous month and estimate from current month to end of year.

[Sean · Accepted solution]

@sdjensen

I got mine to work by modifing the Total when IF ( HASONEVALUE ( Calendar[Month] ) is false

I then basically add the total + the averege to date * (12- completed months) which will be + 0 in a full year

Predictions MEASURE =
IF (
    HASONEVALUE ( 'Calendar'[Month] ),
    IF (
        [Completed Months] < 12,                             // IF NOT Complete Year
        IF (
            MIN ( 'Calendar'[Full Month] ) = "Full Month",   // IF Complete Month
            [Total Measure],                                 // use Actual
            [Completed Months Average]                       // IF NOT use Prediction
        ),
        [Total Measure]                                      // IF Complete Year use Actual
    ),
    [Total Measure]                               // IF in the Total Column
        + ( [Completed Months Average]            // Actual + (Average * Remaining Mos)
        * ( 12 - [Completed Months] ) )           // so IF in a Full Year this will be * 0
)                                                 // leaving just the actual

EDIT: Let me know if you'd like to see the formulas for the other Measures used in the above.

I skipped them because you don't need an Average - probably the only one would be the [Completed Months] so here it is...

Completed Months =
CALCULATE (
    DISTINCTCOUNT ( 'Calendar'[Year-Mo] ),
    ALLEXCEPT ( 'Calendar', 'Calendar'[Year] ),
    'Calendar'[Full Month] = "Full Month"
)

Hope this helps!

[Anonymous]

@Sean

Hi Sean,

How have you calculated the field "full month" in the date dimension / what does this field show?

I am trying creating the same measure. 🙂

[sdjensen]

@Sean - Thank you for all you help. I will stick to the fact solution for now since I think this is a bit cleaner. DAX measures is already messy with currency convertion and scale picker, so to avoid even more complex DAX I calculate my values in the select for my fact tables.

[Sean]

Okay yes I noticed that in my tests too.

I actually don't have projections so instead what I did is create a measure that will count the completed months in each year and then if the completed months are less than 12 I'll calculate the actual average in that year and then try to project that forward.

It works but is not included in the total - the total defaults to the actuals.

I suspect we may have to use VAR-iables for this - at least I would have to.

I have to go now but will look into this later