topic Re: Rolling Last 3 Months Using Dates In Period in DAX Commands and Tips

Rolling Last 3 Months Using Dates In Period

sfalk781 — Fri, 11 Sep 2020 20:18:03 GMT

Hey everyone,

I'm using Dates in Period to look back to get the average of 3 days sales. The measure looks correct to me but the numbers aren't adding up to the right amount. In fact, if I change Average to Sum, it works perfect. Can anyone tell me, with the images provided, why the averages are off?

Moving X Months Avg = CALCULATE(AVERAGE(FactInternetSales21[SalesAmount]),DATESINPERIOD(FactInternetSales21[OrderDate],LASTDATE(FactInternetSales21[OrderDate]),-3,day))

Re: Rolling Last 3 Months Using Dates In Period

Fowmy — Fri, 11 Sep 2020 20:42:59 GMT

sfalk781

Your granularity of the FactInternetSales21 table is not at day level, there are multiple records per dates. You need to summarize. to get the correct average

Moving X Months Avg = CALCULATE( AVERAGEX( SUMMARIZE( FactInternetSales21, FactInternetSales21[Order Date], "Amount",SUM(FactInternetSales21[Sales Amount])), [Amount] ), DATESINPERIOD( FactInternetSales21[Order Date], LASTDATE(FactInternetSales21[Order Date]),-3,day) )

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube LinkedIn

Re: Rolling Last 3 Months Using Dates In Period

sfalk781 — Fri, 11 Sep 2020 20:55:07 GMT

Fowmy thanks for your help. I applied the new measure and it works, but I can't really understand what you're saying. You mentioned that it's not at the "Day" level and there are "multiple" records per dates. In the image I provided, each row is a separate date (day) and I only see a single record for each date.

Can you help explain what I'm missing here?

Re: Rolling Last 3 Months Using Dates In Period

sfalk781 — Fri, 11 Sep 2020 21:01:32 GMT

Fowmy I also wanted to ask, in your formulary, what is actually happening with "amount".

Re: Rolling Last 3 Months Using Dates In Period

Fowmy — Fri, 11 Sep 2020 21:11:17 GMT

sfalk781

What you showed me was the table visual, can you look at the source data in your table?

the formula basically summarizes the table by date and aggregates the value by date, I assigned the total value to a variable called "Amount" which eventually gets averaged.

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube LinkedIn

Re: Rolling Last 3 Months Using Dates In Period

sfalk781 — Fri, 11 Sep 2020 21:25:52 GMT

Fowmy Thanks alot. I looked at the source data which is ficticious data that I was testing with and saw that you were right, there are multiple days of data. My visual was aggregating by total sales amount. As far as the variable "amount" goes, is it standard to use that in all summarize functions?

Re: Rolling Last 3 Months Using Dates In Period

Fowmy — Sat, 12 Sep 2020 03:08:26 GMT

sfalk781

We use SUMMARIIZE column(s) to create a virtual table, it is optional to add an aggegation column along with it like I did with a variable AMOUNT. You can use any name for it.

please refer support documents for further understanding

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube LinkedIn