Comparing data with previous financial year

[Anonymous]

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

[Anonymous]

Thanks so much for the clear explanation, much appreciated! 🙂

[Bibiano_Geraldo]

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
    )

[johnt75]

Have a look at https://www.sqlbi.com/articles/using-weekly-calendars-in-power-bi/ and https://www.daxpatterns.com/week-related-calculations/ 

[shreebidwai · Accepted solution]

• 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])
      )
  )