Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
Attached now
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 !!
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.
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
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 !!
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |