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 (
[NoOfWorkdays],
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.
I reviewed this lesson and my challenge is the calculation of “Average Sales Per Weekday” measure. In my case, the Weekdaytable var in that measure needs to be the number of days for the weeks that customers have sales revenue. All customers don’t have sales revenue for all weeks and I need to capture only the weeks that customers have sales revenue and then, get the number of days for those weeks. This will lead me to calculate the daily average sales per workdays. Would you please guide me how I can achieve this?
Hi Sam,
Just to further clarify, my date table is by weekending (by each Saturday) date so each row represents a week. Each row has the number of workdays for each week. I am wondering how I can build a similar “Weekdaytable” var in “Average Sales Per Weekday” measure which will provide the the number of workdays for the weeks that have sales revenue. Then, I want to calculate the average sales/number of workdays for the current year and prior year by each customer.
I’d really appreciate if you provide any guidance on this.Thanks again.
Okay this is easy because you should not be using a date table set up like this. Please review the below to understand how to get a proper date table into your models and then you’ll be able to solve this as per the video.
If you want to only look at weeks where customers actually have sales, then you want to incorporate some FILTER operation to filter out all of the weeks or days that have no sales for each individual customer.
I would review how to utilise FILTER from the course material and then you’ll understand how you could use this.
Have a go at these and then as always you can add a demo file to get some further assistance. But I would try these first because you will easily be able to solve this if you follow the videos and tutorials that already have solved this previously.