Forum Discussion

BIfanatic's avatar
BIfanatic
Icon for Resolver I rankResolver I
6 years ago

Show average sales for corresponding weekday

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...
Need Help
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

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's avatar
BIfanatic
Icon for Resolver I rankResolver I
6 years ago

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's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

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