Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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))
Try something like:
PatientDiffMeasure = SUM(Patients[Unique Patients]) - CALCULATE(SUM(Patients[Unique Patients]),FILTER(ALL(Patients),[Year]=MAX([Year])-1))
@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.
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.
@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))
Maybe something with your measure calculation, can you share your formula?
You can email me at gdeckler @ fusionalliance . com
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))
OK, I will try to take a look, I did get the email. I was actually asking for your "Unique Patients" measure
Oh, sorry - here it is:
=DISTINCTCOUNT(Table1[PersonID])
OK, I sent back a PBIX attachment, let me know if you get it.
@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.
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))
@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))
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)
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
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.
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.
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 |
PatientDiff = SUM(Patients[Unique Patients]) - CALCULATE(SUM(Patients[Unique Patients]),FILTER(ALL(Patients),[Year]=MAX([Year])-1))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |