Forum Discussion
Help with Year over Year comparison
Hello Everyone,
I apologize if I am asking a question that was already answered, I did search the prior requests before posting this question.
I am trying to understand the best way to show Year over Year comparison, the problem that I encounter is that the counts for the prior year is less than the actual value because things that happened in the prior year may not have happened in the current year and those counts are excluded in the calculation.
Here's an example: I am trying to show patient visits data for current year (Fiscal Year 2025) and compare the counts with prior year (Fiscal Year 2024), let's say Doctor A has seen 2 disease sites GI and lung patients in Fiscal Year 2024, but has only seen lung patients in Fiscal Year 2025, so when I look for Doctor A's counts I only see lung patient counts for both years, how do I make sure I get all the counts for Fiscal Year 2024 (both GI and Lung patients).
I have tried several DAX functions including sameperiodlastyear, Paralellperiod, DATEADD, SelectedValue etc., but I am not getting the result I expect to see. Any help will be greatly appreciated.
P.S: I have filters (slicers) for Fiscal Year, Doctor and Disease site (and other slicers), so when I select Fiscal Year = 2025 and Doctor = A, Disease Site Filters down to only Lung, which is why we are missing GI counts for Fiscal year 2024.
Hope my question is clear.
Thanks,
Charan
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
4 Replies
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
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
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
