Forum Discussion

yan23's avatar
yan23
Frequent Visitor
8 years ago
Solved

Calculate end date

Hello, 

 

I'm having trouble calculating the client's end date of a "phase".
I'm following this logic:
the phase's end date is the initial date of next one

 

                    initial          end 

Phase 1      1/1/2018      5/4/2018

Phase 2       5 /4/2018    5 /4/2018

Phase 3       5/4/2018     6/6/2018
Phase 4        6/6/2018

I'm using this formula but i dont get the results i need

FINTEST = CALCULATE(MIN(Test[FechaEtapa] ),
FILTER(Test, Test[IDPromocion] = FactDocumento[IDPromocion]  && Test[IDCliente] = FactDocumento[IDCliente]&& Test[CodSec] > (FactDocumento[Idsecuencia])))

any help would be great, thank you in advance

  • v-piga-msft's avatar
    v-piga-msft
    8 years ago

    Hi yan23,

     

    You could create a index column in Query Editor first.

     

     

    Then you could create the calculated column with the DAX formula below.

     

    Column =
VAR a =
    CALCULATE (
        MIN ( 'Sheet'[Index] ),
        FILTER ( ALL ( 'Sheet' ), 'Sheet'[Index] > EARLIER ( Sheet[Index] ) )
    )
RETURN
    CALCULATE (
        MAX ( 'Sheet'[initial] ),
        FILTER ( 'Sheet', 'Sheet'[Index] = a )
    )

    Here is the result output.

     

     

    If you need additional help, please share some data sample and your desired output.

     

    Best Regards,

    Cherry

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi

     

    The column you have used in the formula is not present in the sample data, could you please post the sample data for all required colmn ?

     

    Thanks

    Raj

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

    Hi yan23,

     

    You could create a index column in Query Editor first.

     

     

    Then you could create the calculated column with the DAX formula below.

     

    Column =
VAR a =
    CALCULATE (
        MIN ( 'Sheet'[Index] ),
        FILTER ( ALL ( 'Sheet' ), 'Sheet'[Index] > EARLIER ( Sheet[Index] ) )
    )
RETURN
    CALCULATE (
        MAX ( 'Sheet'[initial] ),
        FILTER ( 'Sheet', 'Sheet'[Index] = a )
    )

    Here is the result output.

     

     

    If you need additional help, please share some data sample and your desired output.

     

    Best Regards,

    Cherry

    • yan23's avatar
      yan23
      Frequent Visitor
      8 years ago

      thank you it worked!:smileyhappy: