How to replicate an excel formula in Power BI that self refers the column

[Gui_silva]

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 😃

[tamerj1 · Accepted 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 )

[Gui_silva]

Thank you so much tamerj1.

I hope to get at this level at some point haha!

[Greg_Deckler]

@Gui_silva More than likely not going to happen. Previous Value (“Recursion”) in DAX – Greg Deckler

What are you trying to achieve?

[Gui_silva]

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!

[Greg_Deckler]

@Gui_silva There may be hope then: Cthulhu - Microsoft Power BI Community. If you post sample data as text, I can be more specific.

[Gui_silva]

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