Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Using CALCULATE and FILTER functions to create a Last Week count/sum

Hello,

 

This is hopefully my last post to solve a problem I've been having. Everyone has been really helpful with assisting me step through my goal here. I am now running into trouble with my final calculation of a Last Week distinct count. I cannot use relative date filtering on my view, as I am not using conventional weeks as Power BI sets them with WEEKNUM. 

 

This is my current DAX formula, which makes sense in my head, but does not seem to be filtering my table at all and is counting all rows instead of the intended filtered rows:

 

LW - Total Quotes = 
VAR LWSeq = [Week - Last Seq]
RETURN
CALCULATE([Total - Quotes], FILTER(ALL(Quotes), LWSeq))

 

Where:

 

Total - Quotes = CALCULATE(DISTINCTCOUNT('Quotes'[Quote - Number]),USERELATIONSHIP('Quotes'[Quote - Creation Date],'Calendar'[Date]))
Week - Last Seq = [Week - Current Seq] - 1
Week - Current Seq = 
VAR MaxDate = MAX(Quotes[Quote - Creation Date])
VAR WeekNum = IF(WEEKNUM(MaxDate,1) = 53, 1, WEEKNUM(MaxDate,1))

RETURN
IF(
	IF(WeekNum = 1 && MONTH(MaxDate) = 12, YEAR(MaxDate)+1, YEAR(MaxDate))=2019,
 	WeekNum, 
	WeekNum+52
)

 

 

Its a lot of DAX for what is really a simple concept. Week - Current Seq is simply a sequential week number calculated off of WEEKNUM so that the week number never resets to 1 at the new year (this way WEEK-1 will never = 0 and can refer to week 52). I want to use the Week - Last Seq week number measure in the Calendar to filter the date field in the Quotes table.

 

Example data and expected result:

Table 1   
DateWEEKNUMWeek - Current SeqWeek - Last Seq
2/22/202086059
2/23/202096160
2/24/202096160
2/25/202096160
2/26/202096160
2/27/202096160
2/28/202096160
2/29/202096160
3/1/2020106261
3/2/2020106261
3/3/2020106261
3/4/2020106261
3/5/2020106261
3/6/2020106261

 

Table 2 
Quote#Date
546852/22/2020
546862/23/2020
546872/23/2020
546882/27/2020
546892/27/2020
546902/27/2020
546912/29/2020
546923/2/2020
546933/2/2020
546943/5/2020
546953/6/2020
546963/6/2020
546973/6/2020
546983/6/2020

 

The Week - Current Seq measure uses MAX(Quotes[Quote - Creation Date]) which will be filtered by a slicer that I've verified works. So using the slicer, the LW - Total Quotes results for the total quotes from the sequential week PRIOR should appear as follows:

 

Dates through 2/29/2020: LW - Total Quotes = 1

Dates through 3/7/2020: LW - Total Quotes = 6

Dates through 3/14/2020: LW - Total Quotes = 7

 

Let me know if there are any clarifications I can provide. Again, from all I've read, my first DAX code should be filtering the Quotes table by dates that have the assigned sequential week number of the most recent quote - 1, but I am not getting any quotes filtered out with this DAX formula.

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    For any looking to get an answer for a similar question, I would advise looking at amitchandak's .pbix file to study the calendar and Last Week functions (specifically 'Date'[Week Rank] and 'Date'[Last week Sales]). The issue I was having was actually mulitple issues. I believe that I could have used my existing formulas, but using RANKX() to create the sequential week numbers helped simplify the number of columns and DAX code. 

     

    I also was not thinking in terms of a live report, where a date slicer would also be filtering data to current, so where setting MAX() to the week number, the Last Week count will show a (blank) result without a date slicer because it is looking at the end of the calendar (Sequential week 105) which has yet to happen, therefore no data to show. When a date slicer is applied to a range with data, the funtcion works perfectly.

     

    Thank you amitchandak !!

4 Replies