Forum Discussion
Previous year DAX measure
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))
Community ChampionOK, 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))
- 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)
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