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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
LindaO713
Frequent 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.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

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.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

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.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

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
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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