cancel
Showing results for
Did you mean:
Highlighted
Visitor

## How to store the max date from a slicer as a new metric? (Or workaround it)

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: How to store the max date from a slicer as a new metric? (Or workaround it)

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
)
```
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team

## Re: How to store the max date from a slicer as a new metric? (Or workaround it)

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
)
```
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 425 members 5,061 guests
Recent signins: