Past Week Count of Customers

[celestine_n]

Hi All,

I am trying to calculate the variance between the count of customers on a weekly basis and no I am not able to use the DATE functions as Power BI does not calculate for "Weeks". I am not able to use -7 days as well, as not all month's start/end on the same day. 

Firstly, I would need to get the count of customers from the past week. 

I managed to get the past week net sales to show accurately, and tried to apply the same formula for the count of customers. However, I am not getting the right numbers. 

Please refer to the attached image in which, the figures for PW No. of Customers should reflect the figure from the week before.

However, from the image you can see that for Row 3, 95 is shown instead of 78,

and for Row 4, 99 is shown instead of 81... and so on and so forth.

I would greatly appreciate any form of help.

Formula for Count of Customers: -- Just a distinct count of the customer name field

CY No. of Customers = DISTINCTCOUNT(FACT_Sales[CH2 Customer/Vendor Name])

Formula for Actual Customers: -- Because I have MTD/QTD/YTD slicers

Actual_Customers =

SWITCH('Selected Period'[Selected Period],

1, [CY No. of Customers],

2, CALCULATE([CY No. of Customers],DATESQTD(DIM_Calendar[Date])),

3, CALCULATE([CY No. of Customers],DATESYTD(DIM_Calendar[Date])))

Formula for Past Week count of Customers: 

PW No. of Customers =

VAR CurrentWeek = SELECTEDVALUE(DIM_Calendar[Week of Year])

VAR CurrentYear = SELECTEDVALUE(DIM_Calendar[Year])

VAR CurrentMonth = SELECTEDVALUE(DIM_Calendar[Month])

VAR MaxWeekNumber = CALCULATE(MAX(DIM_Calendar[Week of Year]), FILTER(ALL(DIM_Calendar), DIM_Calendar[Year] = CurrentYear-1))

RETURN

SUMX(

FILTER(ALL(DIM_Calendar),

IF(CurrentWeek = 1, -- If week in current context is 1, find the 53rd week of the prior year

DIM_Calendar[Week of Year] = MaxWeekNumber && DIM_Calendar[Year] = CurrentYear - 1,

DIM_Calendar[Week of Year] = CurrentWeek -1 && DIM_Calendar[Year] = CurrentYear && DIM_Calendar[Month] = CurrentMonth)), -- else

'Measures Table'[Actual_Customers]

)

[amitchandak · Accepted solution]

@celestine_n , for week on week create new column in date table

new columns
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

and measures  like
This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))

Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8

[celestine_n]

Hi @amitchandak,

Thanks for the prompt reply and help!! The issue has been resolved. 

I added in an additional filter at the end to help show only the respective month. 🙂

Here is the code for anyone who needs it:

This Week =

VAR CurrentMonth = SELECTEDVALUE(DIM_Calendar[Month])

RETURN

CALCULATE('Measures Table'[Actual_Customers],

FILTER(ALL(DIM_Calendar),

DIM_Calendar[Week Rank] = MAX(DIM_Calendar[Week Rank]) && DIM_CALENDAR[Month] = CurrentMonth

))

Last Week =

VAR CurrentMonth = SELECTEDVALUE(DIM_Calendar[Month])

RETURN

CALCULATE(

'Measures Table'[Actual_Customers],

FILTER(

ALL(DIM_Calendar),

DIM_Calendar[Week Rank] = MAX(DIM_Calendar[Week Rank])-1 && DIM_CALENDAR[Month] = CurrentMonth

))