Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 🙂
Solved! Go to 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 )
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 )
MErci for the time you take to answer me. indeed I think you have solved my problem. Thanks again for your help 🙂
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 )
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
@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.
User | Count |
---|---|
57 | |
21 | |
19 | |
16 | |
16 |
User | Count |
---|---|
86 | |
77 | |
52 | |
37 | |
22 |