Finding counts for Last Week

[ark]

Hi everyone,

I have a table similar to the below in which data comes in multiple times in a week. The count for a particular Vertical/Domain/Team combination is taken as the count for the latest date for which data is available

Vertical	Domain	Team	Date	Sales Count
Vehicle	Car	Tesla	8/18/2021	17
Vehicle	Car	Tesla	8/16/2021	15
Vehicle	Car	Tesla	8/10/2021	10
Vehicle	Car	Ford	8/17/2021	20
Vehicle	Car	Ford	8/16/2021	18
Vehicle	Car	Toyota	8/10/2021	15
Vehicle	Truck	Man	8/17/2021	8
Vehicle	Truck	Man	8/12/2021	5
Vehicle	Truck	Volvo	8/18/2021	10

I need to create a visualization that would calculate the Sales Count for the last 1 week, that would return the following values for the selected combinations:
Vehicle/Car/Tesla = 7

Vehicle/Car/Toyota = 0

Vehicle/Car/* = 27

Vehicle/Car/Tesla+Toyota = 7

Vehicle/Truck/* = 13

Vehicle/Truck/Man = 3

Is there a way to accomplish this in PowerBI?

[Jos_Woolley]

Thanks. Is your Sales table linked to a standard Date table and, if so, does that Date table contain a column indicating the week number?

Regards

[ark]

The columns mentioned in the post are present in a standard table in a mySQL database.

Week number column is not there. I have added one separately within PowerBI in order to plot some weekly graphs using the below:

WeekNum = WEEKNUM('Table'[Date])

For the weekly graph I have picked the maximum date in a week for particular combination as WeekMaxDate and wherever WeekMaxDate is same as the Date column I'm setting a WeekMaxDateIndicator as 1 using below DAX expressions and using that WeekMaxIndicator as a filter in my graph. (This works but I could not find a way to extend that same logic to find the last week data alone)

WeekMaxDate = CALCULATE(MAX('Table'[Date]),ALLEXCEPT('Table','spaces table_cucumberdata'[WeekNum], 'spaces table_cucumberdata'[Vertical],'Table'[Domain],'Table'[Team]))

WeekMaxDateIndicator = IF('Table'[WeekMaxDate]= 'Table'[Date], 1, 0)

[Jos_Woolley]

Hi,

Can you just clarify who you get your desired result for Vehicle/Car/*?

Also, how is "last 1 week" more rigorously defined?

Regards

[ark]

Last 1 week would be the default Sunday through Saturday. 

For Vehicle/Car/* it would be a summation of cars of all types sold in the current week starting Sunday
17-10=7 for tesla, 20 for Ford and 0 for Toyota giving a total of 27**

**The post said 12 which was a calculation error from my side. Apologies. I've corrected it