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.
Hello everyone!
I have the following measure:
MaxDate = CALCULATE(MAX(Mastercalendar[Date]),filter(Mastercalendar,Mastercalendar[Weekday]<=5))
The objective is to give me the maximum date of the week selected on the slicer stopping Friday (I am not interested on Saturday/Sunday)
Occurences =CALCULATE(COUNT(uvw_GBS_KPIs_HR_v2[hrc_number]),FILTER(ALL(Mastercalendar),Mastercalendar[Date]<=[MaxDate]))
This way I get all the occurences until the maximum dates
My objective is to make a count of occurences that MaxDate - opened_at is less than 15 days and it produces
a % that seems right.
Occurences_15 = CALCULATE([Occurences]
,FILTER(uvw_GBS_KPIs_HR_v2,[MaxDate]-uvw_GBS_KPIs_HR_v2[opened_at]<=15))/[Occurences]
My problem is when I use the following formula:
Occurences_15_30 = (CALCULATE([Occurences]
,FILTER(uvw_GBS_KPIs_HR_v2,([MaxDate]-uvw_GBS_KPIs_HR_v2[opened_at])*1>
15&&([MaxDate]-uvw_GBS_KPIs_HR_v2[_opened_at])*1<=30))
/[Occurences])+0
I used the *1 and also without it, but when I put the conditon between two dates I always have a 0% percentage.
What mistake am I doing here?
Thank you!
Solved! Go to Solution.
The error was I needed to clear some other filters from the mastercalendar.
This works:
CALCULATE(CALCULATE([occurrences],
FILTER(Table,([MaxDate]-uvw_GBS_KPIs_HR_v2[opened_at])*1>15&&
([MaxDate]-Table[LT_opened_at])*1<=30)),ALL(Mastercalendar))
/[occurrences]+0
Occurences_15 = CALCULATE([Occurences]
,FILTER(uvw_GBS_KPIs_HR_v2,[MaxDate]-uvw_GBS_KPIs_HR_v2[opened_at]<=15))/[Occurences]
Occurences_15_30 = (CALCULATE([Occurences]
,FILTER(uvw_GBS_KPIs_HR_v2,([MaxDate]-uvw_GBS_KPIs_HR_v2[opened_at])*1>15&&([MaxDate]-uvw_GBS_KPIs_HR_v2[_opened_at])*1<=30))/[Occurences])+0
These two red highlights are cancelling each other.
The error was I needed to clear some other filters from the mastercalendar.
This works:
CALCULATE(CALCULATE([occurrences],
FILTER(Table,([MaxDate]-uvw_GBS_KPIs_HR_v2[opened_at])*1>15&&
([MaxDate]-Table[LT_opened_at])*1<=30)),ALL(Mastercalendar))
/[occurrences]+0
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 |
---|---|
107 | |
97 | |
75 | |
63 | |
53 |
User | Count |
---|---|
139 | |
100 | |
95 | |
85 | |
63 |