Forum Discussion
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 ...
Jos_Woolley
Solution Sage
Solution SageThanks. 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
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)