cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Gui_silva
New Member

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

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)

Gui_silva_0-1664813628919.png

 

Does anyone know a solution for this?

Thanks in advance!

Gui 😃

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @Gui_silva 
Please refer to attached sample file with the solution

1.png

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 )

View solution in original post

6 REPLIES 6
tamerj1
Super User
Super User

Hi @Gui_silva 
Please refer to attached sample file with the solution

1.png

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!

Greg_Deckler
Super User
Super User

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

 

What are you trying to achieve?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Here's a sample:

DATATIP
02/05/20221
04/05/20221
06/05/20220
08/05/20221
10/05/20220
12/05/20221
14/05/20220
16/05/20220
18/05/20220
20/05/20221
22/05/20221
24/05/20221
26/05/20221
28/05/20221
30/05/20221
01/06/20221
03/06/20221
05/06/20220
07/06/20221
09/06/20220
11/06/20221
13/06/20221
15/06/20221
17/06/20221
19/06/20221
21/06/20221
23/06/20221
25/06/20221
27/06/20221
29/06/20221

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors