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 colum...
- 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])
)
)
- 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])
)
)