topic One row by firstdate in DAX Commands and Tips

One row by firstdate

Anonymous — Fri, 02 Sep 2022 08:40:56 GMT

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 🙂

Re: One row by firstdate

tamerj1 — Fri, 02 Sep 2022 09:51:01 GMT

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 )

Re: One row by firstdate

Anonymous — Fri, 02 Sep 2022 11:26:06 GMT

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

Re: One row by firstdate

tamerj1 — Fri, 02 Sep 2022 12:43:12 GMT

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.

Re: One row by firstdate

tamerj1 — Fri, 02 Sep 2022 13:18:36 GMT

Hi Anonymous
Please use this version instead

Re: One row by firstdate

Anonymous — Fri, 02 Sep 2022 13:42:27 GMT

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