DAX Help, comparing count of rows (appointments) YTD with PY YTD?

[pe2950]

I'm stuck and need some help trying to create a KPI\Scorecard visual on a table of appointments.

Within my table each rown that has a indicator_kept="Y" represents a valid appointment (filter expression). The resulting output should be a count of all the rows for the given period with indicator_kept="Y". 

The date field i am working across is the appointment_date, such that these dates will not be continious (so i can't use SAMEPERIODLASTYEAR(), or at least cant work out how to do so).

Adding to the mix, my appointment table has two relationships to my DateKey table, an appointment_date, and a create_date, the create_date is the default active relationship, so i've also need to use USERELATIONSHIP(appointments[appointment_date],DateKey[Dates]) to specify the correct date relationship. 

So i believe i need 2 measures, one that will show the count for YTD, and one that will show the PYTD. 

DAX:

KPI_Kept_YTD_ = CALCULATE(COUNT(Appointments[appointment_Date])+0,Appointments[ind_kept]="Y", USERELATIONSHIP(DateKey[Date],Appointments[appointment_Date]),DATESYTD(Appointments[appointment_Date]))

KPI_Kept_PYTD_ = CALCULATE(COUNT(Appointments[appointment_Date])+0,Appointments[ind_kept]="Y", USERELATIONSHIP(DateKey[Date],Appointments[appointment_Date]),SAMEPERIODLASTYEAR(DATESYTD(Appointments[appointment_Date])))

I've obviously went wrong somewhere, as my YTD value calculates the correct value (1,028); however my PYTD value is off my a multiple of around 4 (4,053), when it should actually be 1,043.

Can anyone point out where i have gone wrong?

[Anonymous]

If you don't have a proper Date table in your model, you can forget about all the time-intelligence functions. It's as simple as that.

I'd suggest to first get the model RIGHT, then to code. If you start coding in a wrong model, you'll be shooting yourself in the foot. TRUST ME.

Best
D

[ahmedoye]

@pe2950 , kindly confirm the result you have if you just write:

KPI_Kept_PYTD_ = CALCULATE([KPI_Kept_YTD_], SAMEPERIODLASTYEAR(Appointments[appointment_Date]))

Also, I believe it's better your Date Functions such as SAMEPERIODLASTYEAR should reference your Date Table rather than your Data Table.

If this solves your question, kindly kudo and and mark as solution for the benefit of others who may have similar issues.