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 all,
In a report I'm building, I'm attempting to create a couple of measures based on data similar to that in the below table. These measures, both counts, can be called Hikes Started and Hikes Completed.
As a requisite: once a hike is completed, it is no longer counted as a hike start.
For the sample dataset below (and based on a selected date range of 1/3 - 3/31), each metric should be calculated as follows, based on this methodology:
- Hikes Completed (yellow) = 2
- Hikes Started (green) = 3 [since the completed hikes no longer count as 'starts', and the completion date of these hikes is beyond my maximum date in the slicer]
I've been able ot replicate this calculation very easily in Excel, however, my solution there is based on the fact that i can i can easily reference the max date in the slicer.
Can anyone recommend any method by which I could either:
- A) create the two above-mentioned metrics (Hikes Started & Hikes Completed), or
- B) store the Max date chosen in the slicer as a metric?
Thanks in advance!
Solved! Go to Solution.
Add a calendar table and use measures as shown below.
Hikes Completed = CALCULATE ( COUNTROWS ( Table1 ), Table1[Hike Complete Date] IN ALLSELECTED ( 'Calendar'[Date] ) )
Hikes Started = VAR d = MAX ( 'Calendar'[Date] ) RETURN CALCULATE ( COUNTROWS ( Table1 ), Table1[Hike Start Date] IN ALLSELECTED ( 'Calendar'[Date] ), Table1[Hike Complete Date] > d )
Add a calendar table and use measures as shown below.
Hikes Completed = CALCULATE ( COUNTROWS ( Table1 ), Table1[Hike Complete Date] IN ALLSELECTED ( 'Calendar'[Date] ) )
Hikes Started = VAR d = MAX ( 'Calendar'[Date] ) RETURN CALCULATE ( COUNTROWS ( Table1 ), Table1[Hike Start Date] IN ALLSELECTED ( 'Calendar'[Date] ), Table1[Hike Complete Date] > d )
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 |
---|---|
111 | |
94 | |
83 | |
67 | |
59 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |