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.
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?
@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.
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 |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |