Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
quyen103
Frequent Visitor

DAX calculation

Hello friends,

I hope someone can help me out with this problem.  I don't know how I can implement this calculated column in PowerBI.

Should I use DAX? calculated function? data modelling? should I do this in ETL instead of Power BI?

 

Please see exhibit A below.  Smiley Happy 

I have a column (Column3) that needs to be calculated by two other columns from a previous row.  Previous row is denoted by smaller value in Column 0

Not only that, the calculated column would restart for each group of Column 0, so previous row actually gets restart whenever column 0 is 1 again.  I can add another column to denote each group of rows. (for rows with Column0 1-3)

 

Any pointers is greatly appreciated!  Thank you!

 

Column 0

Column 1

Column2

Column3 = ( Column 1 + Column 2 ) of previous row

Restart previous row value when Column 0 value is 1

3

60

600

= 50+500 = 550

2

50

500

= 40 + 400 = 440

1

40

400

 

3

30

300

= 20+200 = 220

2

20

200

= 10 + 100 = 110

1

10

100

 

1 ACCEPTED SOLUTION
Sean
Community Champion
Community Champion

@quyen103

Add an Index Column in the Query Editor as shown in the image below...

Then create this COLUMN

 

Column 3 =
VAR CurIndex = 'Table'[Index]
RETURN
    IF (
        'Table'[Column0] = 1,
        BLANK (),
        CALCULATE (
            SUM ( 'Table'[Column1] ) + SUM ( 'Table'[Column2] ),
            FILTER ( 'Table', 'Table'[Index] = CurIndex + 1 )
        )
    )

 

VAR - DAX Calculation.gif

 

Hope this helps! Smiley Happy

View solution in original post

3 REPLIES 3
Sean
Community Champion
Community Champion

@quyen103

Add an Index Column in the Query Editor as shown in the image below...

Then create this COLUMN

 

Column 3 =
VAR CurIndex = 'Table'[Index]
RETURN
    IF (
        'Table'[Column0] = 1,
        BLANK (),
        CALCULATE (
            SUM ( 'Table'[Column1] ) + SUM ( 'Table'[Column2] ),
            FILTER ( 'Table', 'Table'[Index] = CurIndex + 1 )
        )
    )

 

VAR - DAX Calculation.gif

 

Hope this helps! Smiley Happy

quyen103
Frequent Visitor

Sean,

 

Thank you so much my friend, super contributor Sean 🙂

I really appreciate the video and code response.  Can't get any clearer than this.. !!

Hi @quyen103,

Please mark the right reply as answer. which will help more people to find solution.

Best Regards,
Angelia

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.