Notifications
Clear all
Power BI
3
Posts
2
Users
0
Reactions
100
Views
Topic starter
Hi, within our PBI model we have an existing measure which calculates the number of student results > 80%, divided by the total number of results.
PR = (CALCULATE(COUNT('dt_StudentResults'[Result]), 'dt_StudentResults'[Result]>=.8)/COUNT('dt_StudentResults'[Result]))
The page on which the above is used has slicers/filters on the Academic Year and also on the Quarter - both of which are text fields. We would now like to display on the same visual, the results from the previous year in the same quarter.
As a result, i think the best solution would be to use the SAMEPERIODLASTYEAR, however i'm struggling to format this correctly. For information, i do have a date field in the table.
Here is one of my attempts:
PR-SAMEPERIOD = (CALCULATE(COUNT('dt_StudentResults'[Result]), 'dt_StudentResults'[Result]>=.8)/COUNT('dt_StudentResults'[Result]),SAMEPERIODLASTYEAR('dt_StudentResults'[YearQtrDate].[Date]))
Thanks in advance for any assistance, James
Posted : 29/10/2023 3:22 am
Make sure that you have a Calendar table, linking the date field in dt_StudentResults to the date field in the Calendar. then, build your visual based on Year and Quarter from the Calendar table.
A measure like below should give you the results you want:
=CALCULATE([PR],SAMEPERIODLASTYEAR('Calendar'[Date]))
The attached file includes a pivot table example in Excel that, hopefully, enables you to implement a similar measure in your PowerBI model.
Posted : 29/10/2023 4:28 am
Topic starter
Thank you Riny, i've made some progress using your recommendation and introduced the Calendar Table.
Cheers
Posted : 31/10/2023 7:33 am
