Forum Discussion
Comparing data with previous financial year
Hello all,
I have been asked to adapt an existing report which was using calendar dates to one that uses our company's Financial Years and Financial Weeks.
We have a table which shows a column with the number of orders per calendar month, and another column with the number of orders from the same month for the previous year. For this, I used:
CALCULATE(SUM('Orders'[Order Qty]), SAMEPERIODLASTYEAR('Calendar'[Date]) )
I now need to change this so that it sums the order quantities taken in the same financial week but for the previous financial year. As the "SAMEPERIODLASTYEAR" only works for dates, I'm unsure how to get a measure to give me the data I need. Can anyone help please?
I have the following data Table A:
| FY | FY WeekNum | Customer Number | Orders |
| 2023 | 1 | 104 | 18,026 |
| 2023 | 2 | 104 | 24,941 |
| 2023 | 3 | 104 | 19,161 |
| 2024 | 1 | 104 | 16,831 |
| 2024 | 2 | 104 | 22,954 |
| 2024 | 3 | 104 | 18,977 |
I need to have a table visual which shows the following:
| FY | FY WeekNum | Customer Number | Order | Previous FY |
| 2024 | 1 | 104 | 16,831 | 18,026 |
| 2024 | 2 | 104 | 22,954 | 24,941 |
| 2024 | 3 | 104 | 18,977 | 19,161 |
How can I achieve this please? Any help really appreciated.
Many thanks,
Alison
- Data Model Preparation:
- Ensure your data includes a Financial Year (FY) and Financial Week (FY WeekNum) columns. These will replace the calendar dates for your calculations.
- Create a Measure for Current FY Orders: This sums up orders for the current FY and Week:
DAX
Copy code
Current FY Orders =
SUM('Table A'[Orders]) - Create a Measure for Previous FY Orders: Use DAX to calculate orders for the same week in the previous financial year:
DAX
Copy code
Previous FY Orders =
CALCULATE(
SUM('Table A'[Orders]),
FILTER(
'Table A',
'Table A'[FY] = EARLIER('Table A'[FY]) - 1 &&
'Table A'[FY WeekNum] = EARLIER('Table A'[FY WeekNum])
)
)
4 Replies
- Data Model Preparation:
- Ensure your data includes a Financial Year (FY) and Financial Week (FY WeekNum) columns. These will replace the calendar dates for your calculations.
- Create a Measure for Current FY Orders: This sums up orders for the current FY and Week:
DAX
Copy code
Current FY Orders =
SUM('Table A'[Orders]) - Create a Measure for Previous FY Orders: Use DAX to calculate orders for the same week in the previous financial year:
DAX
Copy code
Previous FY Orders =
CALCULATE(
SUM('Table A'[Orders]),
FILTER(
'Table A',
'Table A'[FY] = EARLIER('Table A'[FY]) - 1 &&
'Table A'[FY WeekNum] = EARLIER('Table A'[FY WeekNum])
)
)
Hi Anonymous ,
If i understood well, please try the bellow DAX measure:
Orders Previous FY = VAR CurrentFY = MAX('Table A'[FY]) VAR CurrentWeekNum = MAX('Table A'[FY WeekNum]) VAR PreviousFY = CurrentFY - 1 RETURN CALCULATE( SUM('Table A'[Orders]), 'Table A'[FY] = PreviousFY, 'Table A'[FY WeekNum] = CurrentWeekNum )
