Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

@Anonymous , 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-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8

 

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@Anonymous , 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-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8

 

Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors