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.
I have created the weighted average which seems to work desirably but I need the weighted average for last 6 months, below are the measures.
In both the cases values do not match to the total for last 6 month weighted average days.
Can anyone suggest me what is the mostake I am doing on this 6 month weighted average and what I may need to change.
Solved! Go to Solution.
Hi @Avivek ,
Do you want to display the average of the past 6 months like the first column or the second column?
If you want to display value like the first column, you can refer the following measure.
Last 6 months Wt Avg =
var _today = TODAY()
var _last6months = DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(TODAY()))
return
CALCULATE([Wt avg 2],FILTER('Table','Table'[Date]>=_last6months && 'Table'[Date]<=_today))
If you want to display value like the second column, you can refer the following measure.
Last 6 Months =
var _today = TODAY()
var _last6months = DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(TODAY()))
var _sumvalue = CALCULATE(SUM('Table'[value]),FILTER(ALLSELECTED('Table'[Date]),'Table'[Date]>=_last6months && 'Table'[Date]<=_today))
var _IF = IF(MAX('Table'[Date])<=_today&&MAX('Table'[Date])>=_last6months,1,0)
return
IF(
_IF=0,BLANK(),
DIVIDE(_sumvalue,6))
If it doesn’t meet your requirement, could you please provide a mockup sample based on fake data or describe the fields of each tables and the relations between tables simply?
It will be helpful if you can show us the exact expected result based on the tables.
Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BTW, pbix as attached.
Hi @Avivek ,
How about the result after you follow the suggestions mentioned in my original post?
Could you please provide more details or expected result about it If it doesn't meet your requirement?
If you've fixed the issue on your own please kindly share your solution. If the above posts help, please kindly mark it as a solution to help others find it more quickly.
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Avivek ,
Do you want to display the average of the past 6 months like the first column or the second column?
If you want to display value like the first column, you can refer the following measure.
Last 6 months Wt Avg =
var _today = TODAY()
var _last6months = DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(TODAY()))
return
CALCULATE([Wt avg 2],FILTER('Table','Table'[Date]>=_last6months && 'Table'[Date]<=_today))
If you want to display value like the second column, you can refer the following measure.
Last 6 Months =
var _today = TODAY()
var _last6months = DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(TODAY()))
var _sumvalue = CALCULATE(SUM('Table'[value]),FILTER(ALLSELECTED('Table'[Date]),'Table'[Date]>=_last6months && 'Table'[Date]<=_today))
var _IF = IF(MAX('Table'[Date])<=_today&&MAX('Table'[Date])>=_last6months,1,0)
return
IF(
_IF=0,BLANK(),
DIVIDE(_sumvalue,6))
If it doesn’t meet your requirement, could you please provide a mockup sample based on fake data or describe the fields of each tables and the relations between tables simply?
It will be helpful if you can show us the exact expected result based on the tables.
Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BTW, pbix as attached.
@Avivek , I saw you posted the same problem in the past. Can you share sample data and sample output in a table format?
Yes @amitchandak , I did try to post this earlier to get a solution but could not find one.I am adding the snapshot of the table with the 6 month weighted average from my report
.
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 |
---|---|
111 | |
94 | |
82 | |
66 | |
59 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |