cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Skunkworked Visitor
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]

 

example.png

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

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

@Skunkworked,

 

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.
1 REPLY 1
Community Support Team
Community Support Team

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

@Skunkworked,

 

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.

Helpful resources

Announcements
Virtual Launch Event

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

Power BI Helps Homeless and Trouble Youth

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

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 425 members 5,061 guests
Please welcome our newest community members: