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 |
---|---|
115 | |
99 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |