The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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.
User | Count |
---|---|
16 | |
8 | |
6 | |
6 | |
5 |
User | Count |
---|---|
23 | |
13 | |
13 | |
8 | |
8 |