Difference between FIRSDATE and FIRSTNONBLANK while getting next row value

[Ondra]

Hello!

I have a formula for a calculated column, which shows date of following row, like this:

NextTariffDate =

IF(

crm_flat_fees[dateuntil] <> BLANK() && RELATED(crm_services[servicetype]) = 1,

CALCULATE (

  FIRSTDATE( crm_flat_fees[datefrom] ),

  FILTER (

    crm_flat_fees,

    crm_flat_fees[datefrom] > EARLIER ( crm_flat_fees[dateuntil] )

   ),

  ALLEXCEPT(crm_flat_fees, crm_flat_fees[projectid])

)

, BLANK()

)

It is working correctly and the result is following (red arrows):

However, I would also like to shift "serviceid" value as indicated by blue arrows. So far I had very little success using FIRSTNONBLANK instead of FIRSTDATE.

Any tips how to do it?

[v-piga-msft · Accepted solution]

Hi @Ondra ,

If I understand your requirement correctly that you want to calculate the next row for serviceid.

You could create a group index column firstly with dax. 

Then create the calculated column with the formula below.

Column = 
CALCULATE (
 FIRSTNONBLANK('Table1'[serviceid],1),
 FILTER (
 'Table1',
 'Table1'[Index] =EARLIER ('Table1'[Index] )-1
 )
)

Here is the output.

You could have a look at this blog to know more about FIRSTNOBLANK().

Best Regards,

Cherry

[Ondra]

Thanks! That's what I was looking for 🙂