Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I have written this formula in excel that is self referred, however in power BI I can't self refer the measure or column that I try to create. The formula is: =IFERROR(IF(B2=0;0;IF(B2+C1>7;1;B2+C1));B2)
Does anyone know a solution for this?
Thanks in advance!
Gui 😃
Solved! Go to Solution.
Hi @Gui_silva
Please refer to attached sample file with the solution
Accumulated Tips =
VAR FirstData = MIN ( 'Table'[DATA] )
VAR CurrentData = 'Table'[DATA]
VAR TableBefore = FILTER ( 'Table', 'Table'[DATA] <= CurrentData )
VAR FilteredTable1 = FILTER ( TableBefore, 'Table'[TIP] = 0 )
VAR MinData = MAXX ( FilteredTable1, 'Table'[DATA] )
VAR FilteredTable2 = FILTER ( TableBefore, 'Table'[DATA] > MinData && 'Table'[DATA] > FirstData )
VAR AccumulatedValue = SUMX ( FilteredTable2, 'Table'[TIP] )
VAR Result1 = MOD ( AccumulatedValue, 7 )
VAR Result2 = IF ( Result1 > 0, Result1, IF ( ISBLANK ( Result1 ), 0, 7 ) )
RETURN
IF ( FirstData = CurrentData, 'Table'[TIP], Result2 )
Hi @Gui_silva
Please refer to attached sample file with the solution
Accumulated Tips =
VAR FirstData = MIN ( 'Table'[DATA] )
VAR CurrentData = 'Table'[DATA]
VAR TableBefore = FILTER ( 'Table', 'Table'[DATA] <= CurrentData )
VAR FilteredTable1 = FILTER ( TableBefore, 'Table'[TIP] = 0 )
VAR MinData = MAXX ( FilteredTable1, 'Table'[DATA] )
VAR FilteredTable2 = FILTER ( TableBefore, 'Table'[DATA] > MinData && 'Table'[DATA] > FirstData )
VAR AccumulatedValue = SUMX ( FilteredTable2, 'Table'[TIP] )
VAR Result1 = MOD ( AccumulatedValue, 7 )
VAR Result2 = IF ( Result1 > 0, Result1, IF ( ISBLANK ( Result1 ), 0, 7 ) )
RETURN
IF ( FirstData = CurrentData, 'Table'[TIP], Result2 )
Thank you so much tamerj1.
I hope to get at this level at some point haha!
@Gui_silva More than likely not going to happen. Previous Value (“Recursion”) in DAX – Greg Deckler
What are you trying to achieve?
Read your post, now I am aware of this limitation.
I was trying to achieve the same resulta that I get in excel. If I receive 1 Tip in a sequence of days, it gets accumulated up to 7, and then returns to 1 if a receive 1 Tip that day or 0 if I dont receive 1 Tip. If I have a break in the sequence of receiving Tips (a day with 0) then it returns to 0. Not sure, if I was clear.
Thanks for replying!
@Gui_silva There may be hope then: Cthulhu - Microsoft Power BI Community. If you post sample data as text, I can be more specific.
Here's a sample:
DATA | TIP |
02/05/2022 | 1 |
04/05/2022 | 1 |
06/05/2022 | 0 |
08/05/2022 | 1 |
10/05/2022 | 0 |
12/05/2022 | 1 |
14/05/2022 | 0 |
16/05/2022 | 0 |
18/05/2022 | 0 |
20/05/2022 | 1 |
22/05/2022 | 1 |
24/05/2022 | 1 |
26/05/2022 | 1 |
28/05/2022 | 1 |
30/05/2022 | 1 |
01/06/2022 | 1 |
03/06/2022 | 1 |
05/06/2022 | 0 |
07/06/2022 | 1 |
09/06/2022 | 0 |
11/06/2022 | 1 |
13/06/2022 | 1 |
15/06/2022 | 1 |
17/06/2022 | 1 |
19/06/2022 | 1 |
21/06/2022 | 1 |
23/06/2022 | 1 |
25/06/2022 | 1 |
27/06/2022 | 1 |
29/06/2022 | 1 |