Show average sales for corresponding weekday

[BIfanatic]

Dear all,

I am looking for a way to design a table where I can both show the sales per date and the average sales for that specific weekday. However, I am running into filter context difficulties that I cannot seem to solve. Consider the table that I would like to design:

Date	Sales	Average sales weekday	%
28/11/2019	500	600	80
30/11/2019	900	600	150
06/12/2019	300	900	33

The first row 28/11/2019 is a Thursday, so in the third column I would like to see the average sales of all Thursdays in the data. Ultimately I can compare both in the fourth column.

Sales is coming from 'Fact table'[Sales], while Date arises from the calendar table column 'Date'[Date]. On this page I also have 'Date'[Quarter] and 'Date'[Year] in a slicer, to add more filter context to the question at hand.

I tried to calculate the average by creating a summarized table, but that did not work. I am pretty cluesless and confused.

Any help is appreciated

[v-shex-msft]

Hi @BIfanatic,

According to your description, it sounds like you want to aggregate your records based on the 'day of week' parameter of the current row context?
If this is a case, you can try to use weekday function as a condition to filter on your tables. (you can also add more conditions to limit this calculation in a specific date period)

measure =
VAR currDate =
    MAX ( Table[Date] )
RETURN
    CALCULATE (
        AVERAGE ( Table[Sales] ),
        FILTER (
            ALLSELECTED ( Table ),
            WEEKDAY ( [Date], 2 ) = WEEKDAY ( currDate, 2 )
        )
    )

Regards,

Xiaoxin Sheng

[amitchandak]

Try like

//Avg before this Date
Cumm Sales = CALCULATE(Average('Fact table'[Sales]),filter(sales,sales[date] <=maxx(date,date[date]) 
	&& weekday(sales[date]) <=weekday(maxx(date,date[date])) ))

Or
//Avg Overall
Cumm Sales = CALCULATE(Average('Fact table'[Sales]),filter(sales,weekday(sales[date]) <=weekday(maxx(date,date[date])) ))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[BIfanatic]

@amitchandak 

Thanks for your reply. It does not really work. It would work if Weekday was in the first column, but I am looking for Date to be the first column.

So, removing filter context from [Date] in the measure, while filtering for [Weekday]. But can not seem to get it done. You have any ideas?

[amitchandak]

It should work with date context. But if that is moved, then we have to stage data using summarize and do it.