rolling average not showing results & not working

[geert12345]

I am trying to get a roll,ing average but i just cannot get it to work:

i have tried the following:

Rolling test = AVERAGEX ( DATESINPERIOD ( SITE[TS].[Date], MAX ( SITE[TS].[Date] ), -7, DAY ), CALCULATE ( SUM ( SITE[PUE] ) ) )

this dit not yield any results (blank), the following code snippits showed the same values as the just calculated average PUE value

Monthly_Rolling_Avg_PUE = VAR NumOfMonths = 2 VAR LastCurrentDate = TODAY() VAR Period = DATESINPERIOD ( SITE[TS].[Date], LastCurrentDate, - NumOfMonths, MONTH ) VAR __DATE_PERIOD = DATESBETWEEN( 'SITE'[TS].[Date], STARTOFMONTH(DATEADD(SITE[TS].[Date], - NumOfMonths, MONTH)), TODAY() ) VAR Result = CALCULATE ( AVERAGEX ( VALUES ( SITE[TS].[Month] ), AVERAGE(SITE[PUE]) ), __DATE_PERIOD ) RETURN Result

,

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... 😞

[geert12345]

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

[Greg_Deckler]

@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])

[Greg_Deckler]

@geert12345 Did you try this? Better Rolling Average - Microsoft Fabric Community

[geert12345]

just did:

Better Rolling Average =

    VAR __EndDate = MAX(SITE[TS].[Date])

    VAR __3MonthsAgo = EOMONTH(__EndDate, -3)

    VAR __StartDate = DATE(YEAR(__3MonthsAgo), MONTH(__3MonthsAgo), 1)

    VAR __Table =

        SUMMARIZE(

            FILTER(ALL(SITE),SITE[TS]>=__StartDate && SITE[TS]<=__EndDate),

            SITE[TS].[Month],

            "__Value",SUM(SITE[PUE])

        )

RETURN

    AVERAGEX(__Table,[__Value])

was the proposed solution, unfortunately i get the following:

[Greg_Deckler]

@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.

[geert12345]

i think i accidentally posted it not as a reply, please find above 

[Greg_Deckler]

@geert12345 Right, did you see my response under where you posted sample data? I attached a PBIX file.

[geert12345]

Hi Greg, 

Yes, i just did, but it does not seem to me like a rolling average? all three columns contain the same values?

[Greg_Deckler]

@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. 

[geert12345]

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?