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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
yforti
Helper II
Helper II

Average over the past 6 months

Hi guys,

I'm having trouble calculating the average for the last 6 months.

I tried this measure and was unsuccessful: 

Msix month =AVERAGEX(DATESINPERIOD(visao_lancamento_setor_Tasy[DT_MOVIMENTO_ESTOQUE],LASTDATE(visao_lancamento_setor_Tasy[DT_MOVIMENTO_ESTOQUE]),-1,MONTH),[vlmovimento])

here's the example:
123123.JPG
The measure should calculate the average of the last 6 months for each NAME  (Nome column), in this example = "Alphamed". 

1 ACCEPTED SOLUTION
v-deddai1-msft
Community Support
Community Support

Hi @yforti ,

 

Are you trying to calculate average between last month and last 7 month? Would you please try the following measure:

Msix month =
AVERAGEX (
    DATESINPERIOD (
        visao_lancamento_setor_Tasy[DT_MOVIMENTO_ESTOQUE],
        EOMONTH ( visao_lancamento_setor_Tasy[DT_MOVIMENTO_ESTOQUE], -1 ),
        -7,
        MONTH
    ),
    [vlmovimento]
)

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Best Regards,

Dedmon Dai

View solution in original post

3 REPLIES 3
v-deddai1-msft
Community Support
Community Support

Hi @yforti ,

 

Are you trying to calculate average between last month and last 7 month? Would you please try the following measure:

Msix month =
AVERAGEX (
    DATESINPERIOD (
        visao_lancamento_setor_Tasy[DT_MOVIMENTO_ESTOQUE],
        EOMONTH ( visao_lancamento_setor_Tasy[DT_MOVIMENTO_ESTOQUE], -1 ),
        -7,
        MONTH
    ),
    [vlmovimento]
)

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Best Regards,

Dedmon Dai

yforti
Helper II
Helper II

@amitchandak i will try to explain myself better

This is my database currently used, the column (% Sector / General Faturavel) is a calculated measure.


Capturara.JPG

I need to find the (% Sector / General Faturavel) of the current month and compare it with the average of the (% Sector / General Faturavel) of the past 6 months.

I did the expected result in excel, see:
Capturar3333.JPG

 

As my database does not yet have data for February, the last month with data is January, so the calculation for the column (Sum 6 previous months)= Dec2020 + Nov2020 + Oct2020 + Sep2020 + Aug2020 + Jul2020.
After the sum of the past 6 months, I averaged this sum of values. This is what I need to do in BI.

To know:
Sum previous 6 months =SUM(D8:D13)
Average previous 6 months =AVERAGE(D8:D13)

amitchandak
Super User
Super User

@yforti , With help from date table

 

Rolling 6 = CALCULATE([vlmovimento],DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-6,MONTH))

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors