Forum Discussion
Help with Year over Year comparison
Hi,
Here’s a step-by-step approach to achieve this:
-
Create a Measure for Total Visits: First, create a measure to calculate the total patient visits without any filters applied.
Total Visits = COUNTROWS('PatientVisits') -
Create a Measure for Visits Last Year: Use the
SAMEPERIODLASTYEAR function to calculate the visits for the same period last year.Visits Last Year = CALCULATE([Total Visits], SAMEPERIODLASTYEAR('Date'[Date])) -
Create a Measure for Visits with All Disease Sites: To ensure you get all counts for Fiscal Year 2024, regardless of the filters applied, use the
ALL function to ignore the Disease Site filter.Visits Last Year All Sites = CALCULATE([Total Visits], SAMEPERIODLASTYEAR('Date'[Date]), ALL('PatientVisits'[DiseaseSite])) -
Combine Measures for Comparison: Now, create a measure to compare the current year’s visits with the previous year’s visits, including all disease sites.
YOY Comparison = IF( ISBLANK([Total Visits]), BLANK(), [Total Visits] - [Visits Last Year All Sites] )
-
Hi Charan_Loya ,
Kaviraj11 , thanks for your concern about this case. I tried to create a sample data myself based on this requirement and implemented the result. Please check if there is anything that can be improved. Here is my solution:
1\I assume there is a table(Table)
2\Add a new caculate table (DiseaseSiteTable) to separate and deduplicate all the disease locations from the original table.
DiseaseSiteTable = DISTINCT('Table'[Disease Site])3\Relate Table and DiseaseSiteTable
4\Add a Matrix table
Best Regards,
Bof
Hi Charan_Loya ,
Kaviraj11 , thanks for your concern about this case. I tried to create a sample data myself based on this requirement and implemented the result. Please check if there is anything that can be improved. Here is my solution:
1\I assume there is a table(Table)
2\Add a new caculate table (DiseaseSiteTable) to separate and deduplicate all the disease locations from the original table.
DiseaseSiteTable = DISTINCT('Table'[Disease Site])
3\Relate Table and DiseaseSiteTable
4\Add a Matrix table
Best Regards,
Bof
Thanks for looking into this and I apologize for the late response. I think I need to give you some more information on what I am looking to achieve.
- Disease site filter is one of the many filters I have in my dashboard, I also have other filters like Visit Location, Visit Type, Visit Specialty etc. that let's the users slice the data to their need.
- I have used the New Card visual to show the selected year visit counts and added a reference label to compare it with prior year visit counts for the selected slicers. Below is a screenshot of how it looks.
- I also have a Matrix visual to show Current Year and Prior Year counts by specialty, I have added Year as column, Specialty as row and counts as values, the counts between the card and matrix for prior year (2024) does not match. This is what I am trying to fix.
Thanks for all your help,
Charan
PBIdashboardsPost Patron
The ALL('PatientVisits'[DiseaseSite]) pattern in the accepted solution is correct for removing the Disease Site filter from the prior year calculation. One edge case to watch: if your slicer is on a related table rather than the fact table, you need REMOVEFILTERS on the relationship path, not just the column.
Extended pattern for multi-slicer YoY:
Visits Last Year All Sites =
CALCULATE(
[Total Visits],
SAMEPERIODLASTYEAR('Date'[Date]),
REMOVEFILTERS('PatientVisits'[DiseaseSite])
)For reporting use cases where clinical teams need to compare periods themselves without IT involvement, Flexa Tables on AppSource lets users select which two periods to compare directly in the published report
