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 all
This is my problem. I am trying to calculate 3 month avarge every month (rolling) with last value.
For exmaple
ID SCORE DATE
2 3 3/19
1 2 4/19
6 3 5/19
3 4 5/19
4 4 6/19
1 3 6/19
6 5 6/19
So May average would be would be 3 (12(Mar,Apr,May)/4) and the value for ID 1=2
But in June average would be would be 3.8 (19(Apr,May,Jun)/5) and the value for ID 1=3
I have search this forum and managed to get to this point(based on this post https://community.powerbi.com/t5/Desktop/Rolling-3-Month-Average/m-p/695325#M335440) , what I need is the way to filter or pass only the lastest values to be used for sum.
Rolling 3msc = VAr PeriodEnd = LASTDATE('Table'[Date]) VAR PeriodStart = FIRSTDATE( DATESINPERIOD('Table'[Date], PeriodEnd, -3, MONTH)) RETURN CALCULATE(SUM('Table'[Score]),DATESBETWEEN ( 'Table'[Date], PeriodStart, PeriodEnd)) # what I need to figure out how to sum the score based on the last score in this period / CALCULATE(DISTINCTCOUNT('Table'[ID]),DATESBETWEEN ( 'Table'[Date], PeriodStart, PeriodEnd))
I would appreciate any help.
Hi Tazmastablasta,
I change your expression for rolling average
Rolling 3msc = VAr PeriodEnd = LASTDATE('avg'[Date]) VAR PeriodStart = FIRSTDATE( DATESINPERIOD('avg'[Date], PeriodEnd, -3, MONTH)) RETURN CALCULATE(SUM('avg'[Score]),DATESBETWEEN ( 'avg'[Date], PeriodStart, PeriodEnd)) / CALCULATE(COUNT('avg'[ID]),DATESBETWEEN ( 'avg'[Date], PeriodStart, PeriodEnd))
It will sum score based on current context in visual, you will see the result is different when I add id in table
But I don't understand the logic of "the value for ID 1=2", if possible, could you please explain this in details?
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Zoe Zhi
Thank you so much for this, The idea is to calulate rolling 3 month avg every month form last submitted score.
* just noticed a mistake in my original post, do apologise
So May average would be would be 3 (12(Mar,Apr,May)/4) and the value for ID 1 Score would be =2
But in June average would be would be 3.8 (19(Apr,May,Jun)/5) and the value for ID1 score would be =3
Like so
ID SCORE DATE
2 3 3/19
1 * 2 4/19
6 3 5/19 May(avg Mar,Apr,May) 3+*2+3+4/4 = 3
3 4 5/19
4 4 6/19 June(avg April May June) 3+4+4+*3+5/5= 3.8
1 * 3 6/19
6 5 6/19
See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...
Thank you so much, I will try.
Hi Tazmzstablasta,
Did this help you solve your issue? If so and if you'd like to, you could mark corresponding post as answer or share your solutions. That way, people who in this forum and have similar issue will benefit from it.
Thanks for your understanding and support.
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Zoe Zhi
Thank you so much for this, but no. My original formula and yours are doing the same thing but The idea is to calulate rolling 3 month avg every month form last submitted score per ID.
So May average would be would be 3 (12(Mar,Apr,May)/4) and the value for ID 1 Score would be =2
But in June average would be would be 3.8 (19(Apr,May,Jun)/5) and the value for ID1 score would be =3
Like so
ID SCORE DATE
2 3 3/19
1 * 2 4/19
6 3 5/19 May(avg Mar,Apr,May) 3+*2+3+4/4 = 3
3 4 5/19
4 4 6/19 June(avg April May June) 3+4+4+*3+5/5= 3.8
1 * 3 6/19
6 5 6/19
And what I am looking for is to Sum only the last submitted value per ID. and calculate 3 month rolling avg.
Do you think you could help me with this ?
Hi
I understand your logic, but if so, I think June(avg April May June) 4+4+*3+5/5= 3.25(16/5), the ID 6 should be 5, right? I will inform you as soon as I get it.
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Yes excaly, sorry for the typo. And thankyou so much for your time !
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 |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |