Forum Discussion
Using CALCULATE and FILTER functions to create a Last Week count/sum
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 !!
Super UserI 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
- amitchandak
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 !!