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'm trying to build a report for showing the las 6 months data. Simplifying what we want:
I have a table with historical data:
I want to show the data like this:
Selecting april 2017,
Selecting may 2017,
Avg: Average of the last six months
Dev: Relative difference between the month value and Avg, (MonthValue-Avg)/Avg. I cannot get this value in PowerBI, I always get 0% for all months.
I've tried using parameters and it works, but in powerbi.com we cannot change them.
I hope you can help me, thanks in advance.
Solved! Go to Solution.
Please refer to the steps below to achieve your requirement.
Result
Regards,
Charlie Liao
@v-caliao-msft wrote:
Please refer to the steps below to achieve your requirement.
- Create calculated columns in your original table.
MonthNumber = SWITCH(Table1[Month],"Jan",1,"Feb",2,"Mar",3,"Apr",4,"May",5,"Jun",6,"Jul",7,"Aug",8,"Sep",9,"Oct",10,"Nov",11,"Dec",12)
Date = DATE(Table1[Year],Table1[MonthNumber],1)- Create a new table by Enter data
- Create acolumn in new created table
YearMonth = FORMAT(Table2[Date],"YYYY-MMM")- Create a measure in new created table
SelectMonth = MAX(Table2[Date])- Create a measure in your original table
DateDifference = 1*(Table2[SelectMonth]-MAX(Table1[Date]))/30- Add this new created measure in your visual filter
Result
Regards,
Charlie Liao
Hi Charlie,
Thanks it worked, with some other measures in my report doesn't work, but for my general purposes it's a great solution.
And, please could you help me with the measure Relative difference?.
Thanks in advance.
This works perfectly, how do i include same month last year.
Please refer to the steps below to achieve your requirement.
Result
Regards,
Charlie Liao
Hi
As per the solution proposed , I tried using the Datedifference as a measure in the visual but it is not allowing me to filter the measure as it is disbaled
Do u have any suggestions for it
Regards
Prabin
@v-caliao-msft wrote:
Please refer to the steps below to achieve your requirement.
- Create calculated columns in your original table.
MonthNumber = SWITCH(Table1[Month],"Jan",1,"Feb",2,"Mar",3,"Apr",4,"May",5,"Jun",6,"Jul",7,"Aug",8,"Sep",9,"Oct",10,"Nov",11,"Dec",12)
Date = DATE(Table1[Year],Table1[MonthNumber],1)- Create a new table by Enter data
- Create acolumn in new created table
YearMonth = FORMAT(Table2[Date],"YYYY-MMM")- Create a measure in new created table
SelectMonth = MAX(Table2[Date])- Create a measure in your original table
DateDifference = 1*(Table2[SelectMonth]-MAX(Table1[Date]))/30- Add this new created measure in your visual filter
Result
Regards,
Charlie Liao
Hi Charlie,
Thanks it worked, with some other measures in my report doesn't work, but for my general purposes it's a great solution.
And, please could you help me with the measure Relative difference?.
Thanks in advance.
Hi dbravo,
Thanks for your help on calculating rolling n months.
I'm new to powerbi, and need your help.
The solution provided by you helps me to calculate rolling n months, but the calendar table I cant use for other calculations.
I need to have a common date filter for all the sheets, and it should work both rolling n months and current month as well.
Can you please help me.
Regards,
Pavan Vanguri.
Could you please share you PBIX, so that we can make further analysis.
Regards,
Charlie Liao
Hi,
I've used the same logic for that measure and it worked.
I created the measure for calculating the average for only the last 6 months with this formula:
Average of last 4 months = CALCULATE(SUM(Data[Value]),FILTER(ALL(Data),and([DateDifference] <= 6,[DateDifference] >= 0)))/CALCULATE(DISTINCTCOUNT(Data[Year Month]),FILTER(all(Data),and([DateDifference] <=6,[DateDifference]>=0)))
Then I created the Relative Difference measure:
Relative Difference = ([All types]-[Average of last 4 months])/[Average of last 4 months]
* [All Types] is a measure that sums all the values on a month.
And it's all. Thank you.
Hi, did you try relative date slicer from preview features?
Maybe this post can help:
Regards,
Happy to help!
Hi,
Thanks for your answer.
I'll use the relative date slicer. But I need to calculate the relative difference too and with a measure I always get 0%. Do you know how I can calculate it?
Bye.
Did you check running the values of the divide separated in order to see if the data of each one is correct?
Maybe you just need to get more decimals and the 0 is a rounded value of the result. You can change this on Format tab.
Regards,
Happy to help!
Covering 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 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |