I am working on in answering a business question and wondering if you would kindly provide some help.
I have sales data by customer by week in FactRevenue table and number of working days per week in DimDate table. DimDate table is by weekending date (Saturday). I need to find the average sales per working day. Ultimate goal is to find gainers, decliners, etc. comparing between YTD average sales per day and PYTD average sales per working day. The challenge is that I am filtering FactRevenue table to find the weeks that have sales revenue (weeks where sales revenue>0) for customers and this is very slow.
NoofEffectiveWorkdays = CALCULATE (
FILTER ( FactRevenue, [Sales Amount ($)] > 0 )
When I am using this measure in subsequent calculations, it’s even slower. I have 50K customers and 200Mil records in FactRevenue table. What I am doing will not work. What is a better, efficient way of doing this? Attached please find Power BI file for your reference. Thank you for your kind help.
SalesAnalysis.pbix (220.1 KB)