cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rolf1994 Regular Visitor
Regular Visitor

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 Super Contributor
Super Contributor

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! Smiley Happy

 

View solution in original post

7 REPLIES 7
Super User
Super User

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

I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Sean Super Contributor
Super Contributor

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! Smiley Happy

 

View solution in original post

rolf1994 Regular Visitor
Regular Visitor

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.

 

Power BI example.png

 

 

Satch Member
Member

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 Member
Member

Re: Calculate an average for last 3 months

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

rtenklooster Frequent Visitor
Frequent Visitor

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.

rtenklooster Frequent Visitor
Frequent Visitor

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!

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 140 members 1,468 guests
Please welcome our newest community members: