Calculate an average for last 3 months

[rolf1994]

Hello,

I want to calculate the following:

the average for the last 3 months and i want to project this data for the rest of the year.

For example: the data for the first 3 months is real data

jan    feb   mar     apr     may     jun    jul

10      20    30        20      20       20     20

I am using a date table

Could someone help me with this?

[Sean · Accepted solution]

@rolf1994

I am assuming you want to do this for Completed Months - so here it goes...

1) Create a COLUMN in your Calendar Table

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

2) And then this MEASURE

3 Completed Months Runnning Total Average =
DIVIDE (
    CALCULATE (
        [MEASURE], // or SUM ( Table[Column] ) //
        DATESINPERIOD (
            'Calendar'[Date],
            CALCULATE (
                LASTDATE ( 'Calendar'[Date] ),
                FILTER ( ALLSELECTED ( 'Calendar' ), 'Calendar'[Full Month] = "Full Month" )
            ),
            -3,
            MONTH
        )
    ),
    3,
    0
)

3) Finally create a MEASURE that will display the correct amount

3 Month Ave Projection Measure =
IF (
    MIN ( 'Calendar'[Full Month] ) = "Full Month",
    [MEASURE], // or SUM ( Table[Column] ) //
    [3 Completed Months Running Total Average]
)

You can now use this Final Measure in a Table Visual to display the real Total for completed Months

AND the current 3 Month Running Total Average for the future Months as projection.

Hope this helps!

[rtenklooster]

Hi @Sean

Thanks for sharing this code. Was looking for this for a few days.

However I'm having an issue with the filter. I can't use the column FullMonth (Autocomplete only let me select the date field)

The error I get is (translated from Dutch) "a single value for the column 'FullMonth' in the table DimDate cannot be determinated. This can happen when the formula of the measurement... "

The FullMonth is a COLUMN in the Dimdate table, it's working the way it should.

My DAX for the MEASURE is:

CALCULATE (
    SUM('Ticket data'[hours_worked]);'Ticket data'[bms]="BMS";
    DATESINPERIOD (
        'Ticket data'[date_worked];
        CALCULATE(
            LASTDATE('DimDate'[Date]);
            FILTER( ALLSELECTED('DimDate'[Date]); 'DimDate'[FullMonth]="Full Month")
        );
        -3;
        MONTH
))

I hope you (or someone else) can help me wih this issue.

[rtenklooster]

Figured it out. Had to use the DimDate table, not the column in the filter expression 😉 Thanks again! works like a charm!

[Satch]

@Sean

Hi, I've made the measure to my dataset, but I get only blank 😞

Any idea where I go wrong?

# Completed Months Running Total Average = 
DIVIDE (
    CALCULATE (
        SUM ( invoiceitem[# Stuks] );
        DATESINPERIOD (
            'date'[Date];
            CALCULATE (
                LASTDATE ('date'[Date] );
                FILTER ( ALLSELECTED ( 'date' ); 'date'[Volle maand] = "Volle Maand" )
            );
            -3;
            MONTH
        )
    );
    3,0
)

[Satch]

Solved: Had to take Orderdate, not date from date table

[rolf1994]

@Sean

Thanks for your answer, it worked just fine. I have one more question:

How can i display a total that matches the data in the projection?

I want it to display a total for all the months showed in the table below.

[Greg_Deckler]

You should be able to create a measure that calculates the average filtered to the first three months and then just use that in your projection. Something like

CALCULATE(AVERAGE([column1]),FILTER(table1,[month]<MONTH(TODAY())))