My DAX code to calculate a moving average is not giving the correct result

Anything related to PowerPivot and DAX Formuale
jagnew
Posts: 5
Joined: Tue Jul 21, 2020 1:56 am

My DAX code to calculate a moving average is not giving the correct result

Postby jagnew » Tue Jul 21, 2020 2:02 am

I'm hoping that someone can help me. I'm wracking my brain on this and just cannot get my moving average to give me the correct results. I first created a daily average to group together a series of dates. I've named this Booking Average and the results that I am getting are correct. The problem is that now I need another average which looks at Booking Average and gives me another average of the last 4 weeks. I created a unique number to group the data but when I try to create the new average it gives me the same average again as the Booking Average. The measure is called CAY average and below is the code I'm using.

=if(countrows(VALUES('Range'[Number])) = 1,
CALCULATE(
AVERAGEX(VALUES('Range'[Number]) ,
'Range'[Booking Average])
,'Range'[Number]<= VALUES('Range'[Number])
&& 'Range'[Number] > VALUES ('Range'[Number])-4)
,BLANK())

I've attached the file. In column I is an example of what the CAY values should be.

Thanks,

Jackie
Attachments
Book4.xlsx
(1.03 MiB) Downloaded 31 times

jagnew
Posts: 5
Joined: Tue Jul 21, 2020 1:56 am

Re: My DAX code to calculate a moving average is not giving the correct result

Postby jagnew » Wed Jul 22, 2020 7:45 am

Anyone have any insight on this? Thanks


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 1 guest