One row by firstdate

[Anonymous]

Hello
I am new to power bi and I have some problems with a report.
I have a table that looks like this:

------------------------------------1-----------------------------------

ID	DateBuy	Frequency	Amount1	Amount2
1A22	22/11/2022	Monthly	170	2.35
1A22	01/12/2022	Monthly	168	2.38
1A38	12/08/2022	Monthly	356	4.55
1A38	17/09/2022	Monthly	356	4.90
1A38	13/10/2022	Monthly	400	5.00
1A46	01/07/2022	Quartely	2300	17.50

With my slicer, if I choose the date : 30/06/2022

Here's what I'd like to have: 

-----------------------------------2------------------------------------

ID	DateBuy	Frequency	Amount1	Amount2
1A22	22/11/2022	Monthly	170	2.35
1A38	12/08/2022	Monthly	356	4.55
1A46	01/07/2022	Quartely	2300	17.50

I tried to create a measure that with FIRSTDATE and I have this result :
-----------------------------------3------------------------------------

ID	DateBuy	Frequency	Amount1	Amount2
1A22	22/11/2022	Monthly	170	2.35
1A22	01/12/2022	Monthly	168	2.38
1A38	12/08/2022	Monthly	356	4.55
1A38	13/10/2022	Monthly	400	5.00
1A46	01/07/2022	Quartely	2300	17.50

So when I remove the Amount 1 & 2 fields I have the result I would like to have (-------2--------)
Please mayday 🙂

[tamerj1 · Accepted solution]

Hi @Anonymous 
Please use this version instead

Filter Measure = 
VAR SelectedDate = SELECTEDVALUE ( 'Date'[Date] )
VAR CurrentDate = CALCULATE ( MAX (Data[DateBuy] ), REMOVEFILTERS ( 'Date' ) )
VAR NextDate = CALCULATE ( MIN ( Data[DateBuy] ), ALLEXCEPT ( Data, Data[ID] ), Data[DateBuy] >= SelectedDate )
RETURN
    IF ( CurrentDate = NextDate, 1 ) 

[Anonymous]

MErci for the time you take to answer me. indeed I think you have solved my problem. Thanks again for your help 🙂

[tamerj1]

Hi @Anonymous 
Please refer to attached sample file with solution 

Filter Measure = 
VAR SelectedDate = SELECTEDVALUE ( 'Date'[Date] )
VAR CurrentDate = CALCULATE ( MAX (Data[DateBuy] ), REMOVEFILTERS ( 'Date' ) )
VAR NextDate = CALCULATE ( MIN ( Data[DateBuy] ), ALLEXCEPT ( Data, Data[ID] ), Data[DateBuy] >= SelectedDate )
RETURN
    DATEDIFF ( CurrentDate, NextDate, DAY ) 

[Anonymous]

Thank you for your quick feedback. however, I have a small problem with your solution. When I choose the date of 02/07/2022 I should no longer have ID: 1A46 available. But it is tjr available in my table

[tamerj1]

@Anonymous 
Actually when you add the measure to the visual it behaves normally

Apparently the measure behaves differently when placed in the filter pane only. I don't have a ready answer for that but I'll look into it.