Need help with Same Period Last Year

[kattlees]

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?

[v-yulgu-msft · Accepted solution]

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.

[parry2k]

@kattlees that is easy but first you have to confirm that you are getting last year values as expected. You havent' confirmed that yet. Once it is done then it is just simple calc.

[kattlees]

I am getting last years numbers if I do the matrix by year.  I am not getting them with any calculations I have tried from above.

[parry2k]

Can you explain what other visuals you tried and what is not working?

[kattlees]

I used v-yulgu-msft suggestion and got the measures for this year and last year but what if I wanted to do a few years and not just 2?

Current year is: Count current year =
CALCULATE (
COUNT ( V_EWS_TASK_RECORD[Year] ),
FILTER ( V_EWS_TASK_RECORD, V_EWS_TASK_RECORD[Year] = YEAR ( TODAY () ) )
)

Previous Year is 

Count last year =
CALCULATE (
COUNT ( [SurgeryCount] ),
FILTER ( V_EWS_TASK_RECORD, V_EWS_TASK_RECORD[Year] = YEAR ( TODAY () ) - 1 )
)

So this gives me 2018 and 2017 but I need to get 2015,2016,2017 numbers (or whatever years I choose) and get the difference.

[parry2k]

@v-yulgu-msft  gave good suggestion but will not work becaue he has fixed date (TODAY())

Use the measure which I gave you and it will work for wharever year you selected and will get you previous year of that year.

[kattlees]

Yours gave me a total of all rows and isn't sorting by surgeon.

Also - the one that uses (today) doesn't count distinct records - it counts all of them.

[parry2k]

I don't think that is true, i just did at my end and it works fine. Something is surely missing at your end, here is screen shot again.

[kattlees]

I greatly appreciate your help but maybe I did something wrong. Here is how the formula is on my end with my table/column names

LY Surgery Count =
CALCULATE(
[Surgery Counts],
All(V_EWS_TASK_RECORD),
PREVIOUSYEAR(V_EWS_TASK_RECORD[ServDate])
)

Surgery Counts is Surgery Counts = DISTINCTCOUNT(V_EWS_TASK_RECORD[SurgeryCount])

SurgeryCount = CONCATENATE(V_EWS_TASK_RECORD[esp1_pat] & "-" & V_EWS_TASK_RECORD[ServDate] & "-", V_EWS_TASK_RECORD[evdet_beg])

Here is screenshot of results

[parry2k]

aha, i think the dataset you gave me doesn't have all the info,can you make following change and test it:

LY Surgery Count =
CALCULATE(
[Surgery Counts],
AllExcept(V_EWS_TASK_RECORD,V_EWS_TASK_RECORD[ServDate] ),
PREVIOUSYEAR(V_EWS_TASK_RECORD[ServDate])
)

 

if doesn't work then share the dataset with all the field you are using.

[kattlees]

I get the same thing.  How do I share a dataset?

[parry2k]

This is weird, share thru google drive or drop box whatever is convenient.

[kattlees]

Can I get your email for dropbox?

[parry2k]

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.

[kattlees]

So 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])
)

[kattlees]

That gives me the total rows in the table. Isn't only getting by year.

This is how it looks:

LY Surgery Count =
CALCULATE(
[Surgery Counts],
All(V_EWS_TASK_RECORD),
PREVIOUSYEAR(V_EWS_TASK_RECORD[ServDate])
)

[parry2k]

not sure why, here it is :