topic Re: Calculating the moving average of previous 4 weeks sales compared to last week sales in DAX Commands and Tips

Calculating the moving average of previous 4 weeks sales compared to last week sales

Anonymous — Wed, 24 Jun 2020 21:15:02 GMT

I have really problem about this:( I hope u can help me about this topic.

Thank u before your answers.

Today=X

I want to solve this porblem with dax.

My data occured

Sales date, Amount and Product type. Each data correleated with each other.

Aim: Weekly moving average of total weekly sales by day, compared to the total of last 4 week sales

X to X-6 total sales amount / (X-7 to X-34)

Re: Calculating the moving average of previous 4 weeks sales compared to last week sales

lbendlin — Wed, 24 Jun 2020 21:51:00 GMT

Use variables to break the problem down

measure =

var today=max([date])

var lastseven = calculate(sum([Sales]),[date]>today-7)

var lastthirtyfive = calculate(sum([Sales]),[date]>today-35)

return divide (lastseven,lastthirtyfive-lastseven)

Adjust the column names as needed.

Re: Calculating the moving average of previous 4 weeks sales compared to last week sales

Anonymous — Wed, 24 Jun 2020 23:39:57 GMT

Hi again,

I am grateful for your help.

I wıll send my page because ı am not solve this problem. It ıs so complicated now:(. I hope u can help me.

My aım:

If today 25.06.2020, (sum of total sales of 25.06.2020 to 19.06.2020) / ((sum of total sales 18.06.2020 to 22.05.2020) /4)

A = sum of total sales of 25.06.2020 to 19.06.2020

B = (sum of total sales 18.06.2020 to 22.05.2020)

A/(B/4)

Re: Calculating the moving average of previous 4 weeks sales compared to last week sales

lbendlin — Wed, 24 Jun 2020 23:12:24 GMT

so you don't want to compare the last seven days to the 28 days before that, but rather to their average. In that case just multiply by 4.

measure = var today=max([date]) var lastseven = calculate(sum([Sales]),[date]>today-7) var lastthirtyfive = calculate(sum([Sales]),[date]>today-35) return divide (4*lastseven,lastthirtyfive-lastseven)

I also see that you have created all separate measures. Please don't do that. The above code has to go into the definition of your single measure. The variables are part of the measure definition.

Re: Calculating the moving average of previous 4 weeks sales compared to last week sales

Anonymous — Wed, 24 Jun 2020 23:39:11 GMT

I thınk smt is wrong on my page,

Maybe my problem is related in [Date]?

On the chart I want to see:

For example: Based on the 14th of May, the ratio of 4 * (8-14th of May total sales) to the sales of 7th May - 16th April. Likewise, I want to see the rate of 13 May. Like all the days of the moon

Re: Calculating the moving average of previous 4 weeks sales compared to last week sales

lbendlin — Thu, 25 Jun 2020 01:05:28 GMT

Don't use a date hierarchy in your facts table - use your Dates table for that. The facts table should have just the raw date field.

In Options and settings, under Current File.. Data load disable the "Auto Date/Time Hierarchy"

Re: Calculating the moving average of previous 4 weeks sales compared to last week sales

Anonymous — Thu, 25 Jun 2020 10:00:40 GMT

Perakende Karşılaştırmalı = (IF( ISFILTERED('Sayfa1'[Date]), AVERAGEX( DATESBETWEEN( 'Sayfa1'[Date], (TODAY()-6), (TODAY()) ), CALCULATE(SUM('Sayfa1'[Sales])) ) ) )/ IF( ISFILTERED('Sayfa1'[Date]), SUMX( DATESBETWEEN( 'Sayfa1'[Date], (TODAY()-34), (TODAY()-7) ), CALCULATE(SUM('Sayfa1'[Sales])) )/4 )