Help with Year over Year comparison

Hi,

Here’s a step-by-step approach to achieve this:

1. Create a Measure for Total Visits: First, create a measure to calculate the total patient visits without any filters applied.

   Total Visits = COUNTROWS('PatientVisits')

2. 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]))

3. 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]))

4. 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