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.
Hi,
I am faced with a challenge of getting the earliest date of a measure in powerbi, since it is a measure i am not able to proceed without column references.
This is a sample dataset,
Apps | Total | Count | Running Count | Percent Covered | Target Reached(>85%) | Date | Earliest Date |
111 | 60 | 47 | 47 | 78% | No | 10/12/2021 | 15/12/2021 |
111 | 60 | 3 | 50 | 83% | No | 13/12/2021 | 15/12/2021 |
111 | 60 | 9 | 59 | 98% | Yes | 15/12/2021 | 15/12/2021 |
111 | 60 | 1 | 60 | 100% | Yes | 17/12/2021 | 15/12/2021 |
124 | 34 | 27 | 27 | 79% | No | 19/12/2021 | 21/12/2021 |
124 | 34 | 3 | 30 | 88% | Yes | 21/12/2021 | 21/12/2021 |
124 | 34 | 2 | 32 | 94% | Yes | 23/12/2021 | 21/12/2021 |
Here, except for App Code, Count, Date, all the other fields are measures. Earliest Date is the field that i wish to calculate
Earliest date is the first date when a running total count specific to an app crosses the 85% threshold.
Solved! Go to Solution.
Does it have to be measures? These facts seem to be immutable (not impacted by user filter choices) so calculated columns should be sufficient.
Percent Covered =
var a = [Apps]
var d = [Date]
return divide(calculate(sum('App Progress'[Count]),ALL('App Progress'),'App Progress'[Apps]=a,'App Progress'[Date]<=d),[Total])
Earliest Date =
var a = [Apps]
return calculate(minx('App Progress','App Progress'[Date]),ALL('App Progress'),'App Progress'[Apps]=a,'App Progress'[Percent Covered]>=0.85)
see attached.
Thanks, you are right. Calculated columns helped me to solve this
Does it have to be measures? These facts seem to be immutable (not impacted by user filter choices) so calculated columns should be sufficient.
Percent Covered =
var a = [Apps]
var d = [Date]
return divide(calculate(sum('App Progress'[Count]),ALL('App Progress'),'App Progress'[Apps]=a,'App Progress'[Date]<=d),[Total])
Earliest Date =
var a = [Apps]
return calculate(minx('App Progress','App Progress'[Date]),ALL('App Progress'),'App Progress'[Apps]=a,'App Progress'[Percent Covered]>=0.85)
see attached.
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 |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |