Forum Discussion

Ondra's avatar
Ondra
Frequent Visitor
7 years ago
Solved

Difference between FIRSDATE and FIRSTNONBLANK while getting next row value

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?
Need Help
  • v-piga-msft's avatar
    v-piga-msft
    7 years ago

    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

2 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Resident Rockstar
    7 years ago

    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's avatar
      Ondra
      Frequent Visitor
      7 years ago

      Thanks! That's what I was looking for :)