Forum Discussion
Need help creating a measure
- 4 years ago
1. I would just create more measures. Making your existing measure more complicated has its own issues, since that might invite performance problems and it will make understanding the measure more complicated for people other than yourself (assuming you have to explain the calculation logic to someone else).
2. The simplest way is to use the same slope. If you indeed have data from 2021 now you can and maybe should use that.
3. Just as a general note you should consider using LY calculation when calculating the differences. Additionally perhaps you could use a table/another visual and use SELECTEDVALUE/MAX for getting the year.
With these in mind an alternative calculation could be something like:
VAR _year =
MAX('454_calendar'[FiscalYear])
VAR _trips =
CALCULATE( [Trips], '454_calendar'[FiscalYear] = _year )
VAR _LYtrips =
CALCULATE(_trips,SAMEPERIODLASTYEAR('calendar'[date]))
VAR _diff =
DIVIDE( _trips - _LYtrips, _LYtrips)
VAR _LYdiff =
CALCULATE(_diff,SAMEPERIODLASTYEAR('calendar'[date]))
VAR _nopan =
DIVIDE( _diff + _LYdiff, 2 )
...
So what I am suggesting is to make your DAX more re-useable. Since you are using LY calculations and difference calculations a lot here I would also consider making calculation groups to make these calculations and utilizing these. For now this is all that comes to my mind and to modify the calculation logic further I would need to see some sample data and desired end result out of this data.
I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!
1. I would just create more measures. Making your existing measure more complicated has its own issues, since that might invite performance problems and it will make understanding the measure more complicated for people other than yourself (assuming you have to explain the calculation logic to someone else).
2. The simplest way is to use the same slope. If you indeed have data from 2021 now you can and maybe should use that.
3. Just as a general note you should consider using LY calculation when calculating the differences. Additionally perhaps you could use a table/another visual and use SELECTEDVALUE/MAX for getting the year.
With these in mind an alternative calculation could be something like:
VAR _year =
MAX('454_calendar'[FiscalYear])
VAR _trips =
CALCULATE( [Trips], '454_calendar'[FiscalYear] = _year )
VAR _LYtrips =
CALCULATE(_trips,SAMEPERIODLASTYEAR('calendar'[date]))
VAR _diff =
DIVIDE( _trips - _LYtrips, _LYtrips)
VAR _LYdiff =
CALCULATE(_diff,SAMEPERIODLASTYEAR('calendar'[date]))
VAR _nopan =
DIVIDE( _diff + _LYdiff, 2 )
...
So what I am suggesting is to make your DAX more re-useable. Since you are using LY calculations and difference calculations a lot here I would also consider making calculation groups to make these calculations and utilizing these. For now this is all that comes to my mind and to modify the calculation logic further I would need to see some sample data and desired end result out of this data.
I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!