How to get start and end dates from Power BI slicer

Question

I have a calendar table and a data table. The data table includes Created_Date and Closed_Date fields. There is a relationship (one to many) from the Date field in the calendar table to the the Created_Date field in the data table. I have a slicer based on the Date field from the calendar table. I have no trouble counting the number of records in the data table where the Created_Date falls in the date range from the slicer. But I'm having a lot of trouble counting the number of records in the data table where the Closed_Date falls within the slicer's date range.

I have tried using both Measures and a Calculated Column and have tried using both CROSSFILTER as well as CALCULATE(MIN(Date),ALLSELECTED(Date)) to get the slicer's start date, but nothing seems to work. Can someone point me in the right direction?

Answer 1

While your question title pertains to getting the min and max dates from a Power BI slicer, your actual question seems to be "how can I count records that match dates in a different date column than my primary relationship".

To do this, you need to:

1. Establish an additional relationship between 'Date'[Date] and 'Table'[Closed_Date]
2. Create a measure that counts rows in 'Table' where this inactive relationship is used instead of the active (default) relationship:

Records matching close date = 
CALCULATE (
    COUNTROWS ( 'Table' ),
    USERELATIONSHIP ( 'Table'[Closed_Date] , 'Date'[Date] )
)

We need to use CALCULATE here since we are applying a forced change to our evaluation context. This forced change is to use a different relationship than our default. The USERELATIONSHIP function then requires us to specify which relationship columns we want to use.

To also answer the question title:

If your slicer uses the 'Date'[Date] column as basis (which it should), then getting the MIN and MAX dates is just simply:

Min Date = MIN ( 'Date'[Date] )

Max Date = MAX ( 'Date'[Date] )