1. I extract sales data every 7 days from SAP into excel. I feed this excel as input for powerBI.
2. I created a table with several columns from different data sources to see information about sales order -->I call this as current week sales order table.
3. I used a date slicer for the current week table. The date slicer range is always
Start date : 01/01/2021 and End date : the day I extract the data.
4. In my table there is a column where I want to apply specific filter of date range. This column is coming from previous week sales order table. The date range is
Start date : 01/01/2021 and End date : 7 days before the end date used in date slicer for the table (end date from point 3)
I am trying to use below formula. I do not know what to use in place or question mark. I do not want to use today()-7 because what if there is a gap of few days or weeks due to any reasons. I want to see the difference in quantity of sales order for current week vs. previous week.
Count of previous week sales order = Calculate(SUM(preweek[sales order]), FILTER(preweek[date]>=01/01/2021 && preweek[date]<=???))
Sample date to explain better :
Current week table :
Start date : 01/Jan/2021 to End date : 01/Feb/2023
| Material | Sum of sales order |
| 5131 | 25 |
| 1221 | 20 |
| 1351 | 53 |
Previous week table :
Start date : 01/Jan/2021 to End date : 25/Jan/2023
| Material | Sum of sales order |
| 5131 | 50 |
| 1221 | 10 |
| 2210 | 5 |
Resultant table
Start date : 01/Jan/2021 to End date : 01/Feb/2023
| Material | Sum sales order (current week) | Sum sales order (previous week) - this will be a calculated column with date filter - 01/Jan/2021 to 25/Jan/2023 |
| 5131 | 25 | 50 |
| 1221 | 20 | 10 |
| 1351 | 35 | null |
Can anyone please help? Trying to find a solution for one week.
Regards,
Bansi
