Help with Measure to return min date in slicer or date in row

[carrl284]

Hi

I have a table as below.

PersonId	Activity	Start	End
1	Working	01/10/2020	14/10/2020
1	Travel	15/10/2020	15/10/2020
1	Travel	25/10/2020	25/10/2020
1	Training	26/10/2020	26/10/2020
1	Absent	27/10/2020	28/10/2020
1	Working	29/10/2020	15/11/2020
2	Working	23/09/2020	13/10/2020
2	Travel	14/10/2020	14/10/2020
2	Travel	23/10/2020	24/10/2020
2	Working	25/10/2020	03/11/2020

I have a between date slicer which uses a disconnected date table and a filter measure so that only rows that contain the dates in the slicer are shown.  This works fine but I would like to know how to write a measure that replaces the Start date with the Min date in the slicer if the start date < date in slicer.  I wrote this measure

Date From =
IF (
MIN ( Activity[Start] ) < MIN ( Dates[Date] ),
MIN ( Dates[Date] ),
MIN ( Activity[Start] )
)

It works fine until I remove the Start and End dates from the table visual, then it groups the activities together.  I would like to know how to write the measure so that all the rows are visible

PersonId	Activity	Date From
1	Working	05/10/2020
1	Travel	15/10/2020
1	Training	26/10/2020
1	Absent	27/10/2020
2	Working	05/10/2020
2	Travel	14/10/2020

Many Thanks 

[Anonymous]

Hi  @carrl284 ，

If your users are willing to receive this answer, please mark our reply.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi  @carrl284 ,

Here are the steps you can follow：

1. Create a calendar table.

Date = CALENDARAUTO()

2. Create measure.

Start1 =
var _selected=SELECTEDVALUE('Date'[Date])
var _min=MAXX(FILTER(ALL('Table'),[PersonId]=MAX('Table'[PersonId])&&[Start]<=_selected),[Start])
var _max=MAXX(FILTER(ALL('Table'),[PersonId]=MAX([PersonId])),[Start])
return
SWITCH(
    TRUE(),
    MAX([Start])=_min,_selected,
    MAX([Start])=_max,_max,
    BLANK())

3. Result.

Start1 is the selected date and will override the latest date

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[carrl284]

Hi @v-yangliu-msft and @amitchandak

Thanks for the measures and the pbix but they don't do what I need but I think that is because as you say @amitchandak my question isn't very clear. If I leave in the original start and end dates I have the context I need. MY users are happy to accept this as a solution. I will work on my questioning for next time.

Thanks again

[amitchandak]

@carrl284 ,

var _max = maxx(allselected('Date') , 'Date'[Date]), can you max date and you can use that in filter as per need

[carrl284]

Let me see if I can clarify, There are 2 rows for PersonId 1 where the activity is Working.  If I filter from 5 Oct - 31 Oct my measure amalgamates those 2 rows into 1 and gives the start date as 5 Oct but I want it to return both rows the first one would start 5 October and the 2nd row would start 29 Oct. I would create a similar measure for End.

[amitchandak]

@carrl284 , Not very clear. Try like

Measure =
var _min = minx(allselected('Date') , 'Date'[Date])
return
calculate(countrows(Table), Filter(Table, Table[Start Date] <_min))