How to calculate Average of the days of Fixed Week of the same month Last year vs the days of the we

[korleones]

Hello,

I am trying to create a measure that calculates the average of the sales of the fixed days of week to compare with the sales of the current month.

Average of Sundays of the last year Fixed compared against the Sundays of the current month

If someone can give me a clue, I would appreciate it.

The graph to use is input per day. Annex examples

[v-danhe-msft]

Hi @korleones,

From your description, I could not understand what you want to calculate? Could you want to calcualte the average sales for weekdays? If so, you could refer to below steps:

Sample data:

Create a calculated column:

Week = WEEKDAY('Table1'[Date])

Create a measure:

Measure = CALCULATE(AVERAGE(Table1[Sales]),FILTER('Table1',WEEKDAY('Table1'[Date])<>6&&WEEKDAY('Table1'[Date])<>7))

Result:

And I could not understand "Average of Sundays of the last year Fixed compared against the Sundays of the current month"

If I understand you incorrectly, could you please offer your sample data and your desired result if possible?

Regards,

Daniel He

[korleones]

Do not explain the problem well, I'm sorry. I am currently trying to create a line chart that shows the Sales per day of the current month, I would like to add a comparative line that shows the Average of the Similar days of a previous month.

The sales of a Monday is not equal to the sales of a Sunday, so I want to compare Monday against Monday, Tuesday against Tuesday ETC

I have tried with the following DAX formula but it only brings me the average of the current month.

Time intelligence functions such as Sameperiorlastyear, Dateadd do not work. Please help me

Averagex General = CALCULATE(AVERAGEX(KEEPFILTERS(ALL('Date'[Date],'Date'[DayOfWeek])),[Averias]),ALL('Date'[Date]))

[korleones]

link Onedrive

https://1drv.ms/x/s!AlqNQINSpZ4VoF7ZgG3Tg0LCsYEO

[korleones · Accepted solution]

Hello,
I already managed to do it. use the following DAX formula:

Average Same Day =
VAR TotalAnopasado =
CALCULATE (
[Total];
FILTER (
ALL ( DimDate );
DimDate[Year]
= MAX ( DimDate[Year] ) - 1
&& DimDate[Month] = MAX ( DimDate[Month] )
&& DimDate[DayOfWeek] = MAX ( DimDate[DayOfWeek] )
)
)
VAR dias =
CALCULATE (
COUNT ( DimDate[Date] );
FILTER (
ALL ( DimDate );
DimDate[Year]
= MAX ( DimDate[Year] ) - 1
&& DimDate[Month] = MAX ( DimDate[Month] )
&& DimDate[DayOfWeek] = MAX ( DimDate[DayOfWeek] )
)
)
RETURN
DIVIDE ( TotalAnopasado; dias; 0 )


I hope it's helpful for the community