Forum Discussion

ark's avatar
ark
Regular Visitor
5 years ago

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

 

VerticalDomainTeamDateSales Count
VehicleCarTesla8/18/2021    17
VehicleCarTesla8/16/2021    15
VehicleCarTesla8/10/2021    10
VehicleCarFord8/17/2021    20
VehicleCarFord8/16/2021    18
VehicleCarToyota8/10/2021    15
VehicleTruckMan8/17/2021    8
VehicleTruckMan8/12/2021    5
VehicleTruckVolvo8/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

  • Jos_Woolley's avatar
    Jos_Woolley
    Solution Sage
    5 years ago

    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's avatar
      ark
      Regular Visitor
      5 years ago

      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

  • Jos_Woolley's avatar
    Jos_Woolley
    Solution Sage
    5 years ago

    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's avatar
      ark
      Regular Visitor
      5 years ago

      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)