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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
ofeliajesus
Helper I
Helper I

Power BI Dax to Calculate the average in 3 periods

Hello,

 

I have two below tables in Dim_Date the Period doesn't correspond to exact months and first I calculated a measure to Divide the count of records per region by NumofWeeks.

The Num of the week is always the same in the same period.

 

ofeliajesus_0-1656510301242.png

Divide per region week =
VAR TotalCountPerRegion = COUNT(Table1[Region])
VAR tblNumOfWeeksInPeriod=
SUMMARIZE(
DimDate
,DimDate[Period]
,DimDate[NumofWeeks]
)

VAR SuMOfWeeksInPeriod = SUMX(tblNumOfWeeksInPeriod,DimDate[NumofWeeks])

RETURN
MROUND(
DIVIDE(
TotalCountPerRegion
,SuMOfWeeksInPeriod
)
,1
)

 

With the formula, I get the following table 

ofeliajesus_1-1656511442581.png

 

I want a new formula to calculate the average of the period with 2 following periods Example
Average 2021_04 = 2021_04 + 2021_05 + 2021_06 = (17+15+9)/3

Average 2021_05 = 2021_05 + 2021_06 + 2021_07 = (15+9+16)/3
and so on.

 

thank you in advance.

 

 

 

2 ACCEPTED SOLUTIONS

Thank you ... 

I want to share a pbix file is it possible seems I don't have an option available to insert a file ... 

am I missing some button?

 

My real measure is more complex because I already have a measure to apply to the 3-month average ...

 

 

 

 

 

 

View solution in original post

I accepted this solution Thank you all

 

[M 3-Period Rolling Avg] =
// Set the number of periods to calculate
// the average over.
var PeriodCount = 3
// First, get the last visible period.
var LastPeriodSeqno = MAX( Dim_Date[PeriodSeqno] )
// Then, get the periods over which to calc.
var PeriodsToAverageOver =
CALCULATETABLE(
DISTINCT( Dim_Date[PeriodSeqno] ),
// Here's the place where you use the fact that
// all the periods are consecutively numbered.
// In fact, the counting does not have to start
// at 1 but it has to increment by 1.
Dim_Date[PeriodSeqno] <= LastPeriodSeqno,
Dim_Date[PeriodSeqno] > LastPeriodSeqno - PeriodCount,
REMOVEFILTERS( Dim_Date )
)
// We need to make sure that there are indeed
// PeriodCount periods in the set. Otherwise,
// the average will not be correct. This could happen
// if we were too close to the beginning of the calendar.
var ShouldCalculate =
COUNTROWS( PeriodsToAverageOver ) = PeriodCount
var Result =
if( ShouldCalculate,
CALCULATE(
AVERAGEX(
PeriodsToAverageOver,
[M]
),
REMOVEFILTERS( Dim_Date )
)
)
return
Result

 

 

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

Hi @ofeliajesus ,

Create a date column first.

dat1e = DATE(LEFT(DimDate[Period],4),RIGHT(DimDate[Period],2),1)

Then create a measure.

Measure11 = 
VAR _MONTH =
    ( EDATE ( MAX ( DimDate[dat1e] ), -3 ) )
RETURN 
   
AVERAGEX(FILTER((DimDate),DimDate[dat1e]>=_MONTH&&DimDate[dat1e]<=SELECTEDVALUE(DimDate[dat1e])),[Quotes per Period])

  Best Regards
Community Support Team _ Polly

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

ofeliajesus
Helper I
Helper I

 

In the Table Measures, I have a formula for Quotes Per period with the DAX that I need to use to apply in the rolling 3 periods starting in 2021_04
The Output will be
2021_06 - 2021_04 + 2021_05 + 2021_06 = (17+15+9)/3
2021_07 - 2021_05 + 2021_06 + 2021_07 = (15+9+16)/3
and so on

I started a new formula in the field trying to join the first calculation with the rolling 3 periods the formula is in Table Measures name Measure

 

DAX to calculate the average in the following 3 periods  

 

thank you

ofeliajesus
Helper I
Helper I

 

I decided to copy/ paste it here 

 

ofeliajesus_2-1656943954608.png

 

 

 

 

 

I have the following Dax 

The divider period is a measure - I want to divide the count per region by number of weeks per period(

# of Weeks)

 

Divider Period =
VAR tblNumOfWeeksInPeriod=
SUMMARIZE(
Dim_Date
,Dim_Date[Period]
,Dim_Date[# of Weeks]
)

VAR SuMOfWeeksInPeriod = SUMX(tblNumOfWeeksInPeriod,Dim_Date[# of Weeks])

Var quotesperweek =
MROUND(
DIVIDE(
'Table Measures'[Total Region]
,SuMOfWeeksInPeriod
)
,1
)
Return
quotesperweek

 

Now I am trying to write the DAX to apply the average of the following 3 periods with the formula that I already had. 

ofeliajesus_1-1656943832188.png

 

Thanks

 

Anonymous
Not applicable

Hi @ofeliajesus ,

Please provide your pbix file without privacy information and desired output.

You can upload the pbix file by Google or Onedrive.

 

How to Get Your Question Answered Quickly 

 

 Best Regards
Community Support Team _ Polly

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

Anonymous
Not applicable

Hi @ofeliajesus ,

I have create a simple sample. Please refer to it to see if it helps you.

Create a date column.

date = DATE(LEFT('Table (2)'[Period],4),RIGHT('Table (2)'[Period],2),1)

Then create a measure.

Measure =
VAR _MONTH =
    ( EDATE ( MAX ( 'Table (2)'[date] ), -3 ) )
RETURN
    CALCULATE (
        AVERAGE ( 'Table (2)'[Divider period] ),
        FILTER (
            ALL ( 'Table (2)' ),
            'Table (2)'[date] >= _MONTH
                && 'Table (2)'[date] <= SELECTEDVALUE ( 'Table (2)'[date] )
        )
    )

vpollymsft_0-1656915745403.png

If I have misunderstood your meaning, please provide your pbix file without privacy information and desired output.

 

 Best Regards
Community Support Team _ Polly

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

Thank you ... 

I want to share a pbix file is it possible seems I don't have an option available to insert a file ... 

am I missing some button?

 

My real measure is more complex because I already have a measure to apply to the 3-month average ...

 

 

 

 

 

 

I accepted this solution Thank you all

 

[M 3-Period Rolling Avg] =
// Set the number of periods to calculate
// the average over.
var PeriodCount = 3
// First, get the last visible period.
var LastPeriodSeqno = MAX( Dim_Date[PeriodSeqno] )
// Then, get the periods over which to calc.
var PeriodsToAverageOver =
CALCULATETABLE(
DISTINCT( Dim_Date[PeriodSeqno] ),
// Here's the place where you use the fact that
// all the periods are consecutively numbered.
// In fact, the counting does not have to start
// at 1 but it has to increment by 1.
Dim_Date[PeriodSeqno] <= LastPeriodSeqno,
Dim_Date[PeriodSeqno] > LastPeriodSeqno - PeriodCount,
REMOVEFILTERS( Dim_Date )
)
// We need to make sure that there are indeed
// PeriodCount periods in the set. Otherwise,
// the average will not be correct. This could happen
// if we were too close to the beginning of the calendar.
var ShouldCalculate =
COUNTROWS( PeriodsToAverageOver ) = PeriodCount
var Result =
if( ShouldCalculate,
CALCULATE(
AVERAGEX(
PeriodsToAverageOver,
[M]
),
REMOVEFILTERS( Dim_Date )
)
)
return
Result

 

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

Top Solution Authors