Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rolf1994
Helper II
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
Sean
Community Champion
Community Champion

@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
Sean
Community Champion
Community Champion

@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

 

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.

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

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

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

@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

 

 

Greg_Deckler
Super User
Super User

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.