cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## Finding counts for Last Week

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?

4 REPLIES 4
Solution Sage

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

Regular Visitor

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

Hi,

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

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

Regards

Regular Visitor

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