Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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:
The measure should calculate the average of the last 6 months for each NAME (Nome column), in this example = "Alphamed".
Solved! Go to Solution.
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
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
@amitchandak i will try to explain myself better
This is my database currently used, the column (% Sector / General Faturavel) is a calculated measure.
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:
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)
@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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
74 | |
63 | |
51 | |
48 |
User | Count |
---|---|
211 | |
86 | |
64 | |
59 | |
56 |