Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Experts
How could i work out the average of the 1st 6 months (as shown) in the same data below.
Appplication Month | Rate | Average First 6 months |
01/02/2020 | 65.2 | 68.5 |
01/03/2020 | 64.4 | |
01/04/2020 | 70.4 | |
01/05/2020 | 70.5 | |
01/06/2020 | 70.4 | |
01/07/2020 | 69.8 | |
01/08/2020 | 68.6 | |
01/09/2020 | 56.8 | |
01/10/2020 | 68.2 | |
01/11/2020 | 70.2 | |
01/12/2020 | 69.2 | |
01/01/2021 | 52.3 | |
01/02/2021 | 44.3 |
Solved! Go to Solution.
@Anonymous
not sure if you want to create a meausre or a column. you only want to show the average of first 6 months?
the solution is based on the sample data you provided, maybe need to be modified for your real data.
Measure =
VAR _MIN=MIN('Table'[Appplication Month])
var _MAX=EDATE(_MIN,6)
RETURN CALCULATE(AVERAGE('Table'[Rate]),FILTER('Table','Table'[Appplication Month]>=_MIN&&'Table'[Appplication Month]<=_MAX))
Proud to be a Super User!
This is my Measure
Aver =
Var _Con = [Conversion]
Var _PeriodEnd = Lastdate(Table[Table Month])
Var _PeriodStart = Firstdate(datesinperiod(Table[Table Month], _PeriodEnd,-6,Month))
Return
Calculate(_Con,Datesbetween(Table[ Table Month],_PeriodStart,_PeriodEnd))
@Anonymous
not sure if you want to create a meausre or a column. you only want to show the average of first 6 months?
the solution is based on the sample data you provided, maybe need to be modified for your real data.
Measure =
VAR _MIN=MIN('Table'[Appplication Month])
var _MAX=EDATE(_MIN,6)
RETURN CALCULATE(AVERAGE('Table'[Rate]),FILTER('Table','Table'[Appplication Month]>=_MIN&&'Table'[Appplication Month]<=_MAX))
Proud to be a Super User!
God Bless You - Thank You....
One more no can solve....
https://community.powerbi.com/t5/Desktop/TRICKY-Diagonal-Sum-Power-Bi-Table/m-p/1935534
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |