Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

One row by firstdate

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

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

IDDateBuyFrequencyAmount1Amount2
1A2222/11/2022Monthly1702.35
1A2201/12/2022Monthly1682.38
1A3812/08/2022Monthly3564.55
1A3817/09/2022Monthly3564.90
1A3813/10/2022Monthly4005.00
1A4601/07/2022Quartely230017.50

 

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

Here's what I'd like to have: 

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

IDDateBuyFrequencyAmount1Amount2
1A2222/11/2022Monthly1702.35
1A3812/08/2022Monthly3564.55
1A4601/07/2022Quartely230017.50

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

IDDateBuyFrequencyAmount1Amount2
1A2222/11/2022Monthly1702.35
1A2201/12/2022Monthly1682.38
1A3812/08/2022Monthly3564.55
1A3813/10/2022Monthly4005.00
1A4601/07/2022Quartely230017.50

 

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

  • tamerj1's avatar
    tamerj1
    4 years ago

    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 )

5 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        4 years ago

        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.

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

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