cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
celestine_n
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.

 

powerbi_help.png

 

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
amitchandak
Super User
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))

 

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

 



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

View solution in original post

2 REPLIES 2
amitchandak
Super User
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))

 

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

 



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

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. 🙂

 

powerbi_solved.png

 

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

Helpful resources

Announcements
Carousel_PBI_Wave1

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.

Power BI Summit Carousel 2

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!

Thank you 2022 Review

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