Forum Discussion

g1davies's avatar
g1davies
Icon for Helper I rankHelper I
8 years ago
Solved

Formula for finding end dates in Power BI

HI,

 

I am trying to find a continuos usage date. in excel i would do it like this:

Column = if it is continuous which I can already achieve, it is the following excel formula I am struggling with.

 

=IF(D3=0,C3,IF(A3=B3,E2,"Something went wrong"))

 

 

How would i do this in power bi?

 

Thanks

 

Gavin 

 

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    HI g1davies,

     

    You can try to use below column formula in power bi to find out grouping start data:

    Start Date =
IF (
    Table[Column] = 0,
    Table[Subscriptions Original Start Date],
    IF (
        Table[Subscriptions Contact ID] = Table[Renamed Col],
        MAXX (
            FILTER (
                ALL ( Table ),
                [Subscriptions Contact ID] = EARLIER ( [Subscriptions Contact ID] )
                    && [Subscriptions Original Start Date]
                        < EARLIER ( [Subscriptions Original Start Date] )
                    && [Column] = 0
            ),
            [Subscriptions Original Start Date]
        ),
        "Something went wrong"
    )
)

    Regards,
    Xiaoxin Sheng

4 Replies

  • g1davies's avatar
    g1davies
    Icon for Helper I rankHelper I
    8 years ago

    HI,

     

    I forgot to say the formula is in the Fudged start date starting in E3, these are correct in the attached excel.

     

    Thanks

     

    Gavin

  • quentin_vigne's avatar
    quentin_vigne
    Icon for Solution Sage rankSolution Sage
    8 years ago

    Hi g1davies

     

    i'm not sure to understand what you want to achieve but I will try : 

     

    Try : 

     

    new calculated column

     

    Column = IF(Table[Column]="0",Table[Subscriptions Original Start Date],IF(Table[Subscriptions Contact ID]=Table[Renamed Col],Table[Fudeged Start],"Something went wrong"))

     

    If this doesn't work we'll need more details like a sample of your data and expected result (values)

     

     

    - Quentin

    • g1davies's avatar
      g1davies
      Icon for Helper I rankHelper I
      8 years ago

      HI Quentin,

       

      Sorry i did not explain very well, THe fudged start date is what i am trying to calculate. PLease see new image which should explain how i am doin this in excel. The problem I have is in excel I use the row above in the same column and I cannont work ou how.

       

      Thanks for you help

       

       

      Thanks

       

      Gavin

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        HI g1davies,

         

        You can try to use below column formula in power bi to find out grouping start data:

        Start Date =
IF (
    Table[Column] = 0,
    Table[Subscriptions Original Start Date],
    IF (
        Table[Subscriptions Contact ID] = Table[Renamed Col],
        MAXX (
            FILTER (
                ALL ( Table ),
                [Subscriptions Contact ID] = EARLIER ( [Subscriptions Contact ID] )
                    && [Subscriptions Original Start Date]
                        < EARLIER ( [Subscriptions Original Start Date] )
                    && [Column] = 0
            ),
            [Subscriptions Original Start Date]
        ),
        "Something went wrong"
    )
)

        Regards,
        Xiaoxin Sheng