Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have the following data (Sample):
Currency Rate ReportingPeriod
AED 0.1 201502
AFN 0.03 201506
Ak1 0.04 201502
AUD 0.01 201603
I am trying to get the average of Rates for the last 12 periods and group them by Currency. The issue I am having is the to get the 12 moving periods (Period is YearMonth) .
Any help or clue on how to do this.
Thank you
Solved! Go to Solution.
Hi @Manar,
If you have a var char you only need to do it with the 99 in the example below my table as monthyear as you have and values are 100 per line as you can see until december 2016 Dec the values are all 1200 before that is reducing 100 per month since the data is only starting in january 2016.
The calculated measure I created was:
calculate = VAR Selected_Month_Year = MAX ( Data[MonthYear] ) RETURN CALCULATE ( SUM ( Data[Value] ); Data[MonthYear] >= Selected_Month_Year - 99 && Data[MonthYear] <= Selected_Month_Year )
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsI would recommend to user power query to modify the data in a well structured way...so that you can come back to it any time..
why dont you seperate year and month using power query , it would then be easy.
Hi @Manar,
Althoug I don't have complete information,
Please check this post where I believe it's a similar problem than yours the only difference is the time that is ni days instead of months.
If you need any additional information please share some data and expected result.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThank you @MFelix The issue I am having is ReportingPeriod is a varchar, so I can't write an expression and use ReportingPeriod - 12 , because even if it subtracts the number I will get doesn't make sense, for example, if Reporting Period is 201803 and I want the average for the last 12 reporting period 201803-12 = 201791 which doesn't make sense it should be 201703
what would you recommend?
Thank you
Hi @Manar,
If you have a var char you only need to do it with the 99 in the example below my table as monthyear as you have and values are 100 per line as you can see until december 2016 Dec the values are all 1200 before that is reducing 100 per month since the data is only starting in january 2016.
The calculated measure I created was:
calculate = VAR Selected_Month_Year = MAX ( Data[MonthYear] ) RETURN CALCULATE ( SUM ( Data[Value] ); Data[MonthYear] >= Selected_Month_Year - 99 && Data[MonthYear] <= Selected_Month_Year )
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |