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
LindaO713
Regular Visitor

Power BI calculated table, new column that adds 1 to previous row value based on condition

Hello: I'm new to Power BI and need a little help. I have searched for what I'm trying to accomplish but haven't quite found a suitable solution.

 

I have a calculated table in Power BI. I'm trying to create a New Column that does the following: If the difference between pickup and next pickup is >90 then previous row value + 1, or else previous row value.

 

New Column = IF('TABLE_All Data Combined'[Days Between Pickup & NextPickup]> 90,"Prev Row Value + 1","Prev Row Value")

 

Below is an example of what I would the results to look like. Any help would be greatly appreciated

Screenshot 2023-06-01 113552.png

2 ACCEPTED SOLUTIONS

Hi,

Thank you for your message, and please try something like below.

 

Jihwan_Kim_0-1685675929645.png

 

 

Expected result newe column CC = 
VAR _startrow =
    MINX( FILTER( Data, Data[Cust ID] = EARLIER( Data[Cust ID])), Data[PickUp Date] )
VAR _addcolumn =
    ADDCOLUMNS (
        Data,
        "@condition",
            SWITCH (
                TRUE (),
                Data[PickUp Date] = _startrow, 1,
                DATEDIFF ( Data[Next Pickup], Data[PickUp Date], DAY ) > 90, 1,
                0
            )
    )
RETURN
    SUMX (
        FILTER (
            _addcolumn,
            Data[PickUp Date] <= EARLIER ( Data[PickUp Date] )
                && Data[Cust ID] = EARLIER ( Data[Cust ID] )
        ),
        [@condition]
    )

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

Jihwan!!! Thank you so much for all your help, this works perfectly!!! I thought I needed to change the VAR startrow to Minx(Filter, but was playing around with it for a while and couldn't figure it out. Your help is greatly appreciated!

View solution in original post

4 REPLIES 4
Jihwan_Kim
Super User
Super User

Hi, 

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

 

Jihwan_Kim_0-1685641695581.png

 

Expected result newe column CC =
VAR _startrow =
    MIN ( Data[PickUp Date] )
VAR _addcolumn =
    ADDCOLUMNS (
        Data,
        "@condition",
            SWITCH (
                TRUE (),
                Data[PickUp Date] = _startrow, 1,
                DATEDIFF ( Data[Next Pickup], Data[PickUp Date], DAY ) > 90, 1,
                0
            )
    )
RETURN
    SUMX (
        FILTER (
            _addcolumn,
            Data[PickUp Date] <= EARLIER ( Data[PickUp Date] )
                && Data[Cust ID] = EARLIER ( Data[Cust ID] )
        ),
        [@condition]
    )

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Hi Jihwan - THANK YOU so much!!!! This almost works! However, I failed to mention that this table has many customers, so I need it to start at 1 for min pickup date for each customer (hope below in "Expected Cust Grouping" column helps demonstate what I'm looking to do)

Screenshot 2023-04-12 162717.png

Hi,

Thank you for your message, and please try something like below.

 

Jihwan_Kim_0-1685675929645.png

 

 

Expected result newe column CC = 
VAR _startrow =
    MINX( FILTER( Data, Data[Cust ID] = EARLIER( Data[Cust ID])), Data[PickUp Date] )
VAR _addcolumn =
    ADDCOLUMNS (
        Data,
        "@condition",
            SWITCH (
                TRUE (),
                Data[PickUp Date] = _startrow, 1,
                DATEDIFF ( Data[Next Pickup], Data[PickUp Date], DAY ) > 90, 1,
                0
            )
    )
RETURN
    SUMX (
        FILTER (
            _addcolumn,
            Data[PickUp Date] <= EARLIER ( Data[PickUp Date] )
                && Data[Cust ID] = EARLIER ( Data[Cust ID] )
        ),
        [@condition]
    )

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Jihwan!!! Thank you so much for all your help, this works perfectly!!! I thought I needed to change the VAR startrow to Minx(Filter, but was playing around with it for a while and couldn't figure it out. Your help is greatly appreciated!

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.

Top Solution Authors