cancel
Showing results for
Did you mean:
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 Date WEEKNUM Week - Current Seq Week - Last Seq 2/22/2020 8 60 59 2/23/2020 9 61 60 2/24/2020 9 61 60 2/25/2020 9 61 60 2/26/2020 9 61 60 2/27/2020 9 61 60 2/28/2020 9 61 60 2/29/2020 9 61 60 3/1/2020 10 62 61 3/2/2020 10 62 61 3/3/2020 10 62 61 3/4/2020 10 62 61 3/5/2020 10 62 61 3/6/2020 10 62 61

 Table 2 Quote# Date 54685 2/22/2020 54686 2/23/2020 54687 2/23/2020 54688 2/27/2020 54689 2/27/2020 54690 2/27/2020 54691 2/29/2020 54692 3/2/2020 54693 3/2/2020 54694 3/5/2020 54695 3/6/2020 54696 3/6/2020 54697 3/6/2020 54698 3/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

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!

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

4 REPLIES 4
Highlighted
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

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!

Highlighted
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

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!

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

Announcements

August 2020 Community Challenge: Can You Solve These?

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

Community Blog

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

Upcoming Events

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

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors