Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
pe2950
Helper I
Helper I

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

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?

2 REPLIES 2
Anonymous
Not applicable

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
Responsive Resident
Responsive Resident

@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.

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.