Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

Anonymous
Not applicable

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
amitchandak
Super User
Super User

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.

Anonymous
Not applicable

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

Attached now

Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.