cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User IV
Super User IV

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

Attached now



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

View solution in original post

Highlighted
Frequent Visitor

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

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 !!

View solution in original post

4 REPLIES 4
Highlighted
Super User IV
Super User IV

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

I have this filter I use Date calendar and rank on Week start date to fins this week and last week.

Can you check if this can help:https://www.dropbox.com/s/d9898a48e76wmvl/sales_analytics_weekWise.pbix?dl=0

 

Appreciate your Kudos.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Frequent Visitor

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

Hello @amitchandak,

 

I'd love to take a look but it seems I'm having trouble accessing the download through the link you provided.

 

Thanks for the quick response!

spham

Highlighted
Super User IV
Super User IV

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

Attached now



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

View solution in original post

Highlighted
Frequent Visitor

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

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 !!

View solution in original post

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors