illusionek
Board Regular
- Joined
- Jun 21, 2014
- Messages
- 104
Hello
I have a calendar and sales tables in a format similar to below, they are connected via Year-Period column.
I am trying to calculate previous month sales and below formula is working but only if I put Period No in my table, what I would like to happen is for that measure to work in all scenarios whether I display data by Period No, Month No or Year-Period.
So far the only way I can make it work is if I amend filter part of my measure to Month No, Year-Period etc.
So I am now not sure if I need three sperate measures for each scenario or the issue is with the way I created the measure in the first place.
Calendar table
Sales Table
I have a calendar and sales tables in a format similar to below, they are connected via Year-Period column.
I am trying to calculate previous month sales and below formula is working but only if I put Period No in my table, what I would like to happen is for that measure to work in all scenarios whether I display data by Period No, Month No or Year-Period.
So far the only way I can make it work is if I amend filter part of my measure to Month No, Year-Period etc.
So I am now not sure if I need three sperate measures for each scenario or the issue is with the way I created the measure in the first place.
Excel Formula:
PreviousMonthSales = Calculate([ActualSales], Calendar[Period No] = Min(Calendar[Period No])-1)| Period No | Actual Sales | Previous Month Sales |
2 | 322 | 0 |
3 | 68 | 322 |
| Month No | Actual Sales | Previous Month Sales |
6 | 322 | 0 |
7 | 68 | 322 |
| Year-Period | Actual Sales | Previous Month Sales |
| 2023-2 | 322 | 0 |
| 2023-3 | 68 | 322 |
Calendar table
| Date | Month No | Period No | Year | Year-Period | Start Date | End Date |
21/06/2023 | 6 | 2 | 2023 | 2023-2 | 01/06/2023 | 30/06/2023 |
17/07/2023 | 7 | 3 | 2023 | 2023-3 | 01/07/2023 | 31/07/2023 |
Sales Table
| Product | Year-Period | Actual Sales |
| A | 2023-2 | 100 |
| B | 2023-3 | 2 |
| C | 2023-2 | 222 |
| A | 2023-3 | 66 |
