Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I am trying to get a roll,ing average but i just cannot get it to work:
i have tried the following:
this dit not yield any results (blank), the following code snippits showed the same values as the just calculated average PUE value
,
this is what the table shows:
Can anyone tell me what i am doing wrong here? bit on the end of my rope.....triend all the ai bots , youtube videos etc... 😞
sure,
here is a small sample (had to strip the rest of the columns if thats not a problem)
TS | PUE |
2019-05-26T00:00:00Z | 1.170.206.609 |
2019-05-26T01:00:00Z | 1.170.994.467 |
2019-05-26T02:00:00Z | 117.201.239 |
2019-05-26T03:00:00Z | 1.171.192.858 |
2019-05-26T04:00:00Z | 1.170.927.058 |
2019-05-26T05:00:00Z | 1.173.085.623 |
2019-05-26T06:00:00Z | 1.179.315.929 |
2019-05-26T07:00:00Z | 1.198.556.758 |
2019-05-26T08:00:00Z | 1.231.947.968 |
2019-05-26T09:00:00Z | 1.255.408.076 |
2019-05-26T10:00:00Z | 1.264.154.404 |
2019-05-26T11:00:00Z | 1.275.710.468 |
2019-05-26T12:00:00Z | 1.279.930.733 |
2019-05-26T13:00:00Z | 1.282.431.505 |
2019-05-26T14:00:00Z | 1.276.957.546 |
2019-05-26T15:00:00Z | 1.269.070.468 |
2019-05-26T16:00:00Z | 1.252.663.137 |
2019-05-26T17:00:00Z | 1.235.230.755 |
2019-05-26T18:00:00Z | 1.198.859.513 |
2019-05-26T19:00:00Z | 1.179.429.371 |
2019-05-26T20:00:00Z | 1.177.617.973 |
2019-05-26T21:00:00Z | 1.176.303.058 |
2019-05-26T22:00:00Z | 117.523.427 |
2019-05-26T23:00:00Z | 1.174.772.471 |
2019-05-27T00:00:00Z | 11.745.778 |
2019-05-27T01:00:00Z | 1.174.173.307 |
2019-05-27T02:00:00Z | 1.172.472.296 |
2019-05-27T03:00:00Z | 1.169.157.413 |
2019-05-27T04:00:00Z | 1.170.025.703 |
2019-05-27T05:00:00Z | 1.171.427.653 |
2019-05-27T06:00:00Z | 1.177.987.189 |
2019-05-27T07:00:00Z | 1.202.353.249 |
2019-05-27T08:00:00Z | 1.218.904.194 |
2019-05-27T09:00:00Z | 1.220.206.211 |
2019-05-27T10:00:00Z | 1.233.766.603 |
2019-05-27T11:00:00Z | 1.238.693.313 |
2019-05-27T12:00:00Z | 1.233.545.972 |
2019-05-27T13:00:00Z | 1.221.802.631 |
2019-05-27T14:00:00Z | 124.729.728 |
2019-05-27T15:00:00Z | 1.257.017.233 |
2019-05-27T16:00:00Z | 1.216.672.049 |
2019-05-27T17:00:00Z | 1.191.469.153 |
2019-05-27T18:00:00Z | 1.176.164.812 |
2019-05-27T19:00:00Z | 1.168.096.201 |
2019-05-27T20:00:00Z | 1.169.333.572 |
2019-05-27T21:00:00Z | 1.169.184.956 |
2019-05-27T22:00:00Z | 1.170.478.438 |
2019-05-27T23:00:00Z | 116.952.877 |
2019-05-28T00:00:00Z | 1.168.542.849 |
2019-05-28T01:00:00Z | 1.169.643.067 |
2019-05-28T02:00:00Z | 1.168.047.869 |
2019-05-28T03:00:00Z | 1.167.997.413 |
2019-05-28T04:00:00Z | 1.167.974.922 |
2019-05-28T05:00:00Z | 1.169.251.431 |
2019-05-28T06:00:00Z | 1.118.266.972 |
2019-05-28T07:00:00Z | 0.9903856238 |
2019-05-28T08:00:00Z | 0.9898190409 |
2019-05-28T09:00:00Z | 0.9951429413 |
2019-05-28T10:00:00Z | 1.002.351.005 |
2019-05-28T11:00:00Z | 101.024.545 |
2019-05-28T12:00:00Z | 1.006.683.564 |
2019-05-28T13:00:00Z | 1.070.634.709 |
2019-05-28T14:00:00Z | 1.218.255.276 |
2019-05-28T15:00:00Z | 1.223.877.478 |
2019-05-28T16:00:00Z | 1.198.409.758 |
2019-05-28T17:00:00Z | 1.184.055.684 |
2019-05-28T18:00:00Z | 1.172.906.517 |
2019-05-28T19:00:00Z | 1.170.151.206 |
2019-05-28T20:00:00Z | 1.168.819.897 |
2019-05-28T21:00:00Z | 1.167.335.287 |
2019-05-28T22:00:00Z | 1.166.646.957 |
2019-05-28T23:00:00Z | 1.171.533.569 |
2019-05-29T00:00:00Z | 1.167.103.933 |
2019-05-29T01:00:00Z | 1.171.756.345 |
2019-05-29T02:00:00Z | 1.176.625.263 |
2019-05-29T03:00:00Z | 1.176.446.733 |
2019-05-29T04:00:00Z | 1.173.884.436 |
2019-05-29T05:00:00Z | 1.172.241.103 |
2019-05-29T06:00:00Z | 1.161.236.197 |
2019-05-29T07:00:00Z | 0.9926167925 |
2019-05-29T08:00:00Z | 0.9935749885 |
2019-05-29T09:00:00Z | 1.026.904.875 |
2019-05-29T10:00:00Z | 1.239.050.106 |
2019-05-29T11:00:00Z | 1.229.148.439 |
2019-05-29T12:00:00Z | 1.231.313.592 |
2019-05-29T13:00:00Z | 1.234.214.091 |
2019-05-29T14:00:00Z | 1.235.635.648 |
2019-05-29T15:00:00Z | 1.239.537.253 |
2019-05-29T16:00:00Z | 124.192.799 |
2019-05-29T17:00:00Z | 1.233.952.458 |
2019-05-29T18:00:00Z | 1.201.162.226 |
2019-05-29T19:00:00Z | 1.178.659.197 |
2019-05-29T20:00:00Z | 1.173.551.923 |
2019-05-29T21:00:00Z | 1.175.705.501 |
2019-05-29T22:00:00Z | 1.178.186.138 |
2019-05-29T23:00:00Z | 1.178.819.276 |
2019-05-30T00:00:00Z | 1.176.037.376 |
2019-05-30T01:00:00Z | 1.172.466.635 |
2019-05-30T02:00:00Z | 1.172.416.242 |
2019-05-30T03:00:00Z | 1.171.652.645 |
@geert12345 Pretty sure it's the auto-time intelligence stuff. This seems to work for me. PBIX is attached below signature. Word to the wise, just disable auto-time intelligence, it will bring you nothing but pain and suffering.
Better Rolling Average =
VAR __EndDate = MAX(SITE[TS])
VAR __3MonthsAgo = EOMONTH(__EndDate, -3)
VAR __StartDate = DATE(YEAR(__3MonthsAgo), MONTH(__3MonthsAgo), 1)
VAR __Table =
SUMMARIZE(
ADDCOLUMNS(FILTER(ALL(SITE),SITE[TS]>=__StartDate && SITE[TS]<=__EndDate),"__Month", MONTH([TS])),
[__Month],
"__Value",SUM(SITE[PUE])
)
RETURN
AVERAGEX(__Table,[__Value])
@geert12345 Did you try this? Better Rolling Average - Microsoft Fabric Community
just did:
@geert12345 Can you post some sample data as text? One thing I would highly recommend, avoid the the .[Date] and .[Month] notation. That's auto time intelligence and will lead to nothing but misery.
i think i accidentally posted it not as a reply, please find above
@geert12345 Right, did you see my response under where you posted sample data? I attached a PBIX file.
Hi Greg,
Yes, i just did, but it does not seem to me like a rolling average? all three columns contain the same values?
@geert12345 I don't see that at all other than the total and that's a whole other issue. Also, the first row but that makes total sense. The average of one date is going to be the sum on that date. Besides, you only gave me information for May but the formula is for a 3 month rolling average so it's going to be kind of jacked up. You just need to modify the __3MonthsAgo variable to something like __7DaysAgo and just use __EndDate - 7 for example.
Hi Greg,
Apologies! you are right, my mistake! but great example!Iit
two questions: the rolling seems to show a rolling average of the summed total, how do i get the rolling average of the average? (switching to average did not seem to be helpng)
Also, PowerBI desktop is really not loving this, loading times are really slow, can it go faster?
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
17 | |
16 | |
15 | |
14 | |
11 |
User | Count |
---|---|
36 | |
26 | |
20 | |
19 | |
17 |