topic Re: Power BI calculated table, new column that adds 1 to previous row value based on condition in DAX Commands and Tips

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

LindaO713 — Thu, 01 Jun 2023 15:37:44 GMT

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

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

Jihwan_Kim — Thu, 01 Jun 2023 17:48:52 GMT

Hi,

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

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] )

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

LindaO713 — Thu, 01 Jun 2023 22:50:59 GMT

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)

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

Jihwan_Kim — Fri, 02 Jun 2023 03:19:20 GMT

Hi,

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

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] )

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

LindaO713 — Fri, 02 Jun 2023 12:37:34 GMT

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!