Forum Discussion
Resolver IPrevious Week Sales - Logic (cumulative)
Hello I encounter this request and having a hard time creating a dax formula for this.
so basically I have this table
| Date | Day of Week | Sales |
| 25-Sep | Sunday | 123 |
| 26-Sep | Monday | 213 |
| 27-Sep | Tuesday | 32 |
| 28-Sep | Wednesday | 2 |
| 29-Sep | Thursday | 23 |
| 30-Sep | Friday | 23 |
| 1-Oct | Saturday | 23 |
| 2-Oct | Sunday | 123 |
| 3-Oct | Monday | 123 |
| 4-Oct | Tuesday | 232 |
| 5-Oct | Wednesday | 32 |
| 6-Oct | Thursday | 3 |
| 7-Oct | Friday | 23 |
| 8-Oct | Saturday | 2 |
| 9-Oct | Sunday | 32 |
| 10-Oct | Monday | 3 |
| 11-Oct | Tuesday | 23 |
The goal is to get the total sales last week based on where we at this week.
Lets assume today is 10/12 (wednesday), basically we will only have data up to tuesday.
The goal is to get the sum of last week from sunday to tuesday (again last week)(upto tuesday only because today is wednesday).
So basically the answer should be 478
which is the sum of this from last week (sunday to tuesday last week) - this is because today is Wednesday (in our testing or in this case)
| Date | Day of Week | Sales |
| 2-Oct | Sunday | 123 |
| 3-Oct | Monday | 123 |
| 4-Oct | Tuesday | 232 |
so if today's date is 10/15 (saturday) the result should be - 536
Here's the explanation table for it
| Date | Day of Week | Sales |
| 2-Oct | Sunday | 123 |
| 3-Oct | Monday | 123 |
| 4-Oct | Tuesday | 232 |
| 5-Oct | Wednesday | 32 |
| 6-Oct | Thursday | 3 |
| 7-Oct | Friday | 23 |
ChristianDGreat , As want to display Last week Trend , Make sure there is no date filter, or if there is date filter it should come from independent date table
LWTD Today =
var _min = TODAY() -WEEKDAY(TODAY(),1) -6 //Sumday week start
var _max = today() -7
return CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))LWTD Today =
//Date1 is independent Date table, Date is joined with Table
var _today = maxx(allselected(Date1),Date1[Date])
var _min =_today -WEEKDAY(_today ,1) -6 //Sunday week start
var _max = _today -7
return CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))Need of an Independent Table in Power BI - Exclude: https://youtu.be/lOEW-YUrAbE
1 Reply
- amitchandak
Super User
ChristianDGreat , As want to display Last week Trend , Make sure there is no date filter, or if there is date filter it should come from independent date table
LWTD Today =
var _min = TODAY() -WEEKDAY(TODAY(),1) -6 //Sumday week start
var _max = today() -7
return CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))LWTD Today =
//Date1 is independent Date table, Date is joined with Table
var _today = maxx(allselected(Date1),Date1[Date])
var _min =_today -WEEKDAY(_today ,1) -6 //Sunday week start
var _max = _today -7
return CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))Need of an Independent Table in Power BI - Exclude: https://youtu.be/lOEW-YUrAbE