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