cancel
Showing results for
Did you mean:
rolf1994 Helper II

## Calculate an average for last 3 months

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Sean Community Champion

## Re: Calculate an average for last 3 months

@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! 7 REPLIES 7 Super User IV

## Re: Calculate an average for last 3 months

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())))`

---------------------------------------

Putting square pegs in round holes since 1972.

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Datanaut!

Sean Community Champion

## Re: Calculate an average for last 3 months

@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! rolf1994 Helper II

## Re: Calculate an average for last 3 months

@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. Satch Helper III

## Re: Calculate an average for last 3 months

@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 Helper III

## Re: Calculate an average for last 3 months

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

Highlighted
New Member

## Re: Calculate an average for last 3 months

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.

New Member

## Re: Calculate an average for last 3 months

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