Previous year DAX measure

[Anonymous]

Hi - I'm trying to create a measure that calculates the previous year's value i.e. for 2015 it would show 346 (6617-6271). 

I've tried SAMEPERIODLASTYEAR and PARALLELPERIOD with no success - the "year" column is a product of powerpivot auto generating separate date fields (month, quarter, year) based on a single date column in the data i.e. The "year" in the table below is actually ServiceDate(Year) in the field list and is based off a field called ServiceDate. Hoping someone can help out with this, thanks in advance!

Year                  Unique Patients 

2014	6,271
2015	6,617
2016	6,544
2017	6,118

[Greg_Deckler]

Try something like:

PatientDiffMeasure = SUM(Patients[Unique Patients]) - CALCULATE(SUM(Patients[Unique Patients]),FILTER(ALL(Patients),[Year]=MAX([Year])-1))

[Anonymous]

 @Greg_Deckler thanks for your suggestion - unique patients is a measure so it won't let me put it in a SUM; would you be willing to share your email? I have the file in dropbox and could share it with you.

[Greg_Deckler]

Oh, just take out the SUM's then if it is already a measure. You can use a measure in another measure without an aggregation.

[Anonymous]

@Greg_Deckler - the formula returns a result, but not what I'm looking for; tried a few different things with no success, not sure what the issue is. any chance I can share the file with you? 

Year            UniquePts   Diff  

2014	54	52
2015	43	43
2016	32	31
2017	30	30

=[Unique Patients] - CALCULATE([Unique Patients], FILTER(ALL(Table1), [ServiceDate] = MAX([ServiceDate])-1))

[Greg_Deckler]

Maybe something with your measure calculation, can you share your formula? 

You can email me at gdeckler @ fusionalliance . com

[Anonymous]

thanks very much - I shared the file through dropbox; let me know if you didn't get it.

here's the current formula: 

=[Unique Patients] - CALCULATE([Unique Patients], FILTER(ALL(Table1), [ServiceDate] = MAX([ServiceDate])-1))

[Greg_Deckler]

OK, I will try to take a look, I did get the email. I was actually asking for your "Unique Patients" measure

[Anonymous]

Oh, sorry - here it is: 

=DISTINCTCOUNT(Table1[PersonID])

[Greg_Deckler]

OK, I sent back a PBIX attachment, let me know if you get it.

[Anonymous]

@Greg_Deckler  got your attachment, unfortunately can't open it with my current version of PBI; I don't want to make it a hassle for you, if it's easiest to just send over the formulas you wrote, that's perfectly fine with me. Thank you again. 

[Anonymous · Accepted solution]

resolution in case it helps someone else: 

PatientsRaw = table name

Unique Patients = DISTINCTCOUNT(PatientsRaw[PersonID])

UniquePatientDiff = [Unique Patients] - CALCULATE([Unique Patients],FILTER(ALLEXCEPT(PatientsRaw,PatientsRaw[PayorName]),[CalendarYear]=MAX([CalendarYear])-1))

[Anonymous]

@Greg_Deckler - quick follow up to this case: I'm looking to graph the difference metric and was wondering if there's a way to make the 2014 result blank instead of 6,271.

      Unique Patients       Unique Patient Difference

2014	6,271	6,271
2015	6,617	346
2016	6,544	-73
2017	6,118	-426

Ideally, something like this would be the output - this is what happens if I choose "Difference from prior year" in the "show values

as" menu. 

Row Labels  Difference   

2014
2015	346
2016	-73
2017	-426

This is the current formula: 

=[Unique Patients] - CALCULATE([Unique Patients],FILTER(ALLEXCEPT(Sheet2,Sheet2[PayorName]),[CalendarYear]=MAX([CalendarYear])-1))

[Greg_Deckler]

Try something like this:

Column = VAR myVar = CALCULATE([Unique Patients],FILTER(ALLEXCEPT(Sheet2,Sheet2[PayorName]),[CalendarYear]=MAX([CalendarYear])-1))

RETURN IF(ISBLANK(myVar),BLANK(), [Unique Patients] - myVar)

[Anonymous]

Hi Greg,

Is there a way to display a measure results for current year and last 2 years dynamically?

Meaning, the user doesnt have to select any year but the report will automatically default to current year and last 2 years 

[mattbrice]

SAMEPERIODLASTYEAR and PARALLELPERIOD both require a column of date values to work - you can't use on 'Year' column.  Otherwise you need to post more info for someone to help.  Or a file with sample data.

[Anonymous]

Thanks for the prompt reply - how can I go about posting a file with sample data? I see options to insert code or a link, but none to attach a file. Sorry for the basic question, I'm new to this forum. 

[Anonymous]

Hi - I'm trying to create a measure that calculates the previous year's value i.e. for 2015 the value would be 6617-6271 = 346

I"ve tried using SAMEPERIODLASTYEAR as well as PARALLELPERIOD and can't get either to work. The "year" columns is a result of the automatic date separation that PowerPivot does - it's called ServiceDate(Year) in the field list and comes from a single "ServiceDate" field. Hoping someone can help me out with this, thanks in advance!

Year      Unique Patients

2014	6,271
2015	6,617
2016	6,544
2017	6,118

[Greg_Deckler]

PatientDiff = SUM(Patients[Unique Patients]) - CALCULATE(SUM(Patients[Unique Patients]),FILTER(ALL(Patients),[Year]=MAX([Year])-1))