Average daily sales by no of days lapsed (with a slight hiccup)

[alau]

Hello fellow BI'er, I am new to BI and in desperate need of some assistance to accurately calculate average daily sales.

I have a date table in my pbix, along with an actual transactional sales table, which shows by day by sales orders by various customers. Now, I want to visualise the average daily sales by each customer.

I first created a "Work Days Lapsed" measure:

Work Days Lapsed = CALCULATE(

COUNTROWS ( 'Date Dimension'),

DATESBETWEEN ( 'Date Dimension'[FullDateAlternateKey], FIRSTDATE(ACTUALS[Date]), LASTDATE(ACTUALS[Date]) ),

'Date Dimension'[Workday] = 1)

Then I created a "Daily Actual Gross Sales" measure:

Daily ACT Gross Sales = CALCULATE(DIVIDE(sum(ACTUALS[ACT Gross Sales]),[Work Days Lapsed]))

Where I run into trouble is not every customer make a purchase daily. So instead of dividing the total month-to-date sales by the total number of work days lapsed so far this month, in those instances, my BI formula is dividing the month-to-date sales by the number of work days that a customer has made a purchase.

To illustrate, here is a dummy data table with 2 customers:

DATE	Cust A	Cust B	TOTAL
1/01/2020	$          50		$          50
2/01/2020	$          20	$        400	$        420
3/01/2020	$          50		$          50
4/01/2020	$        100		$        100
5/01/2020	$          30		$          30
6/01/2020	$          20	$          20	$          40
7/01/2020	$          10		$          10
	$        280	$        420	$        700
			7	Number of days with sales
			$        100	Avg $ sales per day

But instead, this is what I am seeing:

	Cust A	Cust B	TOTAL
Number of days with sales	7	2	7
Avg $ sales per day	$          40	$        210	$        100

What change to my DAX do I need to get it to calculate correctly?

Thanks so much!

[amitchandak]

@alau , join the actual date with a date table and use that

For Avg MTD try a measure like given example with date table

Avg MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date])) / CALCULATE(Distinctcount('Date'[Date]),DATESMTD('Date'[Date]), not(isblank(Sales[Sales Amount])))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[alau]

Thanks for the quick reply. I just tried it, but it didn't give the desire outcome. I thnk it's because I did not make myself 100% clear...

Is there anyway I can get it to divide by the number of workdays lapsed so far? In the current DAX, I can see that it is dividing the MTD sales by the number of days in the month (eg. 30 days in Nov). But what if I am 7 sales day into the month... how do i get BI to divide the MTD sale by 7 days? 

Thanks!

[v-janeyg-msft]

Hi， @alau 

Is this result incorrect? What‘s the desired result you want?

Best Regards

Janey Guo

[alau]

Hi Janey, thanks for the reply.

Here is what I am trying to do...

So my DAX is treating the average daily sales for Customer B's sales as over 2 days, instead of the total of 7 days lapsed. So obviously my DAX is not correct/or specific enough. 

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[alau]

That worked! Thank you so much @Ashish_Mathur for taking the time to attach a working pbix also!!!

You have no idea how long I've been stuc on this problem =P

I even managed to add a whope heap more "ALL" conditions to your Days with sales across customers formula so that it resolves any problems with products, locations, sales reps, etc. 

Thank you thank you thank you!

[Ashish_Mathur]

You are welcome.