Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am trying to calculate Sales This period compared to Sales Previous period, which is a simple formula I know. But what's complicated about this, is the client works on Retail Calendars. So for example their Fiscal year starts on the 1st of March.
So Period 1 is therefore March. But every year's calendar does not necessarily run from 01/03 - 31/03.
Here are examples of how their year's calendars for March (AKA Period 1) run:
Fiscal Year 2023: 01/03 - 29/03
FY2022: 01/03 - 26/03
FY2021: 01/03 - 26/03
FY2020: 01/03/ - 27/03 Etc.
So because of this complication, I am not able to use SAMEPERIODLASTYEAR, as this compares day to day. I am looking for something like Period to Period.
How would I Calculate Period 1 this year vs Period 1 Last Year?
You need to change the syntax slightly:
Sales PY Parallel = CALCULATE('Sales Fact Table'[Total Sales],PARALLELPERIOD('Fiscal Calendar'[Date Field],-1,Year))
The -1 indicates the interval and you want Year instead of Month as your periods are a year apart.
I'm not sure if 'Fiscal Calendar'[Fiscal Period] is a date field, but the expression requires the field to be a date data type. Instead of the text string that presumably makes up 'Period 1', you could create a date column, if there is not one already, with values such as 01/03/2019 for '01/03/2019-27/03/2019', so that everything for that period is recorded on the first day of the month. Then you could use this field in the DAX expression - Having said this, this date field should allow you to use SAMEPERIODLASTYEAR.
If you don't have the date field already, you could extract this from the text string using LEFT (https://docs.microsoft.com/en-us/dax/left-function-dax) and DATEVALUE (https://docs.microsoft.com/en-us/dax/datevalue-function-dax)
Thanks for this. Can I ask you to kindly elaborate, as I don't quite understand?
What would the formula look like? I tried this, but I think I'm misunderstanding you.
Think PARALLELPERIOD will help you here: https://docs.microsoft.com/en-us/dax/parallelperiod-function-dax
Once you have period 1, you can use this with PARALLELPERIOD to get the same period for the prior year.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |