Forum Discussion
Post PatronNeed help with Same Period Last Year
I'm trying to do a matrix to show a surgeon and then how many surgeries they did in a year and then how many they did the year before.
To count surgeries, I have
Measure: Surgery Counts = DISTINCTCOUNT(V_EWS_TASK RECORD[PATNUM])
For last year I have
Measure: Surgery Counts LY = CALCULATE([surgery Counts],V_EWS_TASK_RECORD[Year]-1)
Year is Year = V_EWS_TASK_RECORD[ServDate].[Year] - this is a whole number column and if I try to convert to date, it puts all 1905 in.
My LY Measure is giving me the same number as my current year each time.
If I change Surgery Counts LY to = CALCULATE([surgery Counts],SAMEPERIODLASTYEAR(V_EWS_TASK_RECORD[ServDate].[Year])) I get A column specified in the call to function SAMEPERIODLASTYEAR is not of type DATE.
What do I do?
Hi kattlees,
Please try below measures:
Count current year = CALCULATE ( COUNT ( V_EWS_TASK_RECORD[Year] ), FILTER ( V_EWS_TASK_RECORD, V_EWS_TASK_RECORD[Year] = YEAR ( TODAY () ) ) ) Count last year = CALCULATE ( COUNT ( V_EWS_TASK_RECORD[Year] ), FILTER ( V_EWS_TASK_RECORD, V_EWS_TASK_RECORD[Year] = YEAR ( TODAY () ) - 1 ) )Then, you can use a table visual to display data.
If you use a matrix visual, it would be easier, there is no need to create measures.
Best regards,
Yuliana Gu
19 Replies
- parry2k
Super User
Understood your post what you are looking for but unable to understand your data model. Could you please post sample data here with column name etc.
kattleesSo Data would be:
Dr. PATNUM ServDate Year
Dr. S 123456 5/15/2016 2016
Dr. S 854678 6/1/2016 2016
Dr. S 854666 6/1/2016 2016
Dr. S 985674 4/10/2017 2017
Dr. S 156729 5/15/2017 2017
Dr. S 189888 3/22/2017 2017
Dr. S 546726 11/12/2017 2017
Data would show
Doctor count current year count last year
Dr. S 4 3
- parry2k
Try this:
LY Surgery Count = CALCULATE( [Surgery Count], All(Table2), PREVIOUSYEAR(Table2[ServDate]) )
v-yulgu-msftMicrosoft Employee
Hi kattlees,
Please try below measures:
Count current year = CALCULATE ( COUNT ( V_EWS_TASK_RECORD[Year] ), FILTER ( V_EWS_TASK_RECORD, V_EWS_TASK_RECORD[Year] = YEAR ( TODAY () ) ) ) Count last year = CALCULATE ( COUNT ( V_EWS_TASK_RECORD[Year] ), FILTER ( V_EWS_TASK_RECORD, V_EWS_TASK_RECORD[Year] = YEAR ( TODAY () ) - 1 ) )Then, you can use a table visual to display data.
If you use a matrix visual, it would be easier, there is no need to create measures.
Best regards,
Yuliana Gu
- kattlees
Maybe I am explaining this wrong. I have a matrix with surgeon and # of cases per year.
My ultimate goal is to show the % of increase/decrease from year to year. See screen shot. I just figured I needed a count last year measure to use in the calculation for percentage. Is there any other way to do it?
My count measure is Surgery Counts = DISTINCTCOUNT(V_EWS_TASK_RECORD[SurgeryCount])
My SurgeryCount column is SurgeryCount = CONCATENATE(V_EWS_TASK_RECORD[esp1_pat] & "-" & V_EWS_TASK_RECORD[ServDate] & "-", V_EWS_TASK_RECORD[evdet_beg]) as I have to get distinct records. Someone may have 2 surgeries in a day at different times and I have to count them as 2 but if they have two surgeries at the same time it only counts as 1.
