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.
Hey All,
I need to create a measure to achieve a rolling semester rate based off the previous two quarters. I've used PARALLELPERIOD but that can only yield results by quarter. Below is an example of my data - I have the actual rate and the measure I need is the applied rate which would be the average from the previous semester. Any help would be tremendously appreciated!
Solved! Go to Solution.
I attached the PBIX file below because had to do some work in Power Query to get the data set up since there is no dedicated Calendar table. Easier to look at the file instead of me listing out all the steps.
The end result is:
That Index column above is what I was going after. That Calculated Column is:
Index = VAR CurrentDate= 'RollingSemester'[Date] RETURN CALCULATE( DISTINCTCOUNT('RollingSemester'[FySemID]), FILTER( ALL( 'RollingSemester'), CurrentDate >= RollingSemester[Date] ) )
With that Index, I can set the min and max of the previous semester:
Min of Prev Index = CALCULATE( MIN('RollingSemester'[Date]), FILTER( ALL( 'RollingSemester'), RollingSemester[Index] = MAX( RollingSemester[Index]) -1 ) ) Max of Prev Index = CALCULATE( MAX('RollingSemester'[Date]), FILTER( ALL( 'RollingSemester'), RollingSemester[Index] = MAX( RollingSemester[Index]) -1 ) )
Now that we have the min and max of what we want to average, can use that a few ways. I decided to use DATESBETWEEN:
Average of Actual Rate = AVERAGE(RollingSemester[Actual Rate]) Applied Rate = IF( MAX('RollingSemester'[Index]) <> 1, CALCULATE( [Average of Actual Rate], DATESBETWEEN( 'RollingSemester'[Date], [Min of Prev Index], [Max of Prev Index] ) ) )
Then if you want to show the actual rate if there, or the average if not in one column can use:
Actual and Applied one Measure = IF ( ISBLANK([Average of Actual Rate]),[Applied Rate],[Average of Actual Rate])
Might not be 100% what you were looking for, but should hopefully be a good starting point
can you add that data so I can grab it? Don't feel like typing it
@Anonymous let me know if this works for you, thanks for giving it a look!
Month Fiscal Quarter Year Actual Rate Applied Rate 4/1/2018 Q3 2018 18% 5/1/2018 Q3 2018 15% 6/1/2018 Q3 2018 12% 7/1/2018 Q4 2018 22% 8/1/2018 Q4 2018 9% 9/1/2018 Q4 2018 18% 10/1/2018 Q1 2019 15.67% 11/1/2018 Q1 2019 15.67% 12/1/2018 Q1 2019 15.67% 1/1/2019 Q2 2019 15.67% 2/1/2019 Q2 2019 15.67% 3/1/2019 Q2 2019 15.67% 4/1/2019 Q3 2019 5/1/2019 Q3 2019 6/1/2019 Q3 2019
It does. I'm just not entirely sure what you are after. How'd you get that 15.67% number?
@Anonymous it's the average of the previous semester which is the measure I'm trying to create
I attached the PBIX file below because had to do some work in Power Query to get the data set up since there is no dedicated Calendar table. Easier to look at the file instead of me listing out all the steps.
The end result is:
That Index column above is what I was going after. That Calculated Column is:
Index = VAR CurrentDate= 'RollingSemester'[Date] RETURN CALCULATE( DISTINCTCOUNT('RollingSemester'[FySemID]), FILTER( ALL( 'RollingSemester'), CurrentDate >= RollingSemester[Date] ) )
With that Index, I can set the min and max of the previous semester:
Min of Prev Index = CALCULATE( MIN('RollingSemester'[Date]), FILTER( ALL( 'RollingSemester'), RollingSemester[Index] = MAX( RollingSemester[Index]) -1 ) ) Max of Prev Index = CALCULATE( MAX('RollingSemester'[Date]), FILTER( ALL( 'RollingSemester'), RollingSemester[Index] = MAX( RollingSemester[Index]) -1 ) )
Now that we have the min and max of what we want to average, can use that a few ways. I decided to use DATESBETWEEN:
Average of Actual Rate = AVERAGE(RollingSemester[Actual Rate]) Applied Rate = IF( MAX('RollingSemester'[Index]) <> 1, CALCULATE( [Average of Actual Rate], DATESBETWEEN( 'RollingSemester'[Date], [Min of Prev Index], [Max of Prev Index] ) ) )
Then if you want to show the actual rate if there, or the average if not in one column can use:
Actual and Applied one Measure = IF ( ISBLANK([Average of Actual Rate]),[Applied Rate],[Average of Actual Rate])
Might not be 100% what you were looking for, but should hopefully be a good starting point
@Anonymous thanks a ton man, this is perfect!
I had tried using the datesinperiod function based off a semester start date, but creating a pairing end date and using datesbetween works much better. Really appreciate the help.
Glad it helped!
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 |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |