cancel
Showing results for
Did you mean:
Frequent Visitor

## Past Week Count of Customers

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]
)
1 ACCEPTED SOLUTION
Super User

@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))

Learn Power BI - Full Course with Dec-2022, with new DAX functions like Window, Index, Offset !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Formatted Profit and Loss Statement with empty lines
2 REPLIES 2
Super User

@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))

Learn Power BI - Full Course with Dec-2022, with new DAX functions like Window, Index, Offset !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Formatted Profit and Loss Statement with empty lines
Frequent Visitor

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
))

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### 2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors