March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
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
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.
Solved! Go to Solution.
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
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.
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
I decided to copy/ paste it here
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(
Now I am trying to write the DAX to apply the average of the following 3 periods with the formula that I already had.
Thanks
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.
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] )
)
)
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
62 | |
27 | |
25 | |
22 | |
16 |