The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello all,
can you please advise how to build a measure to calculate the % of time a value was above a threshold not binding to abosolute date values, only relative - like in last N days?
My case - I have sales data per day per salesman and a threshold of sales he should be making daily (example data)
John - 01.04.20 - 62K - threshold 70K
John - 02.04.20 - 72K - threshold 70K
....
John - 30.04.20 - 92K - threshold 70K
I need see how many days he met the threshold, e.g. in N=30 then 14/30 ~46.67%, if N=15 then 14/15 then 93.34%
Thanks for any hints or advice!
Solved! Go to Solution.
Hi @Mariusz,
something like following:
Person | Date | Sales | Threshold |
John | 10.04.20 | 42 | 40 |
John | 11.04.20 | 30 | 55 |
John | 12.04.20 | 21 | 50 |
John | 13.04.20 | 42 | 45 |
John | 14.04.20 | 48 | 50 |
John | 15.04.20 | 53 | 50 |
John | 16.04.20 | 50 | 60 |
John | 17.04.20 | 70 | 65 |
John | 18.04.20 | 62 | 60 |
John | 19.04.20 | 60 | 45 |
Under threshold in bold, so
In last N=5 days - 1/5 = 20%
In last N=10 days - 5/10 = 50%.