Forum Discussion

PrathSable's avatar
PrathSable
Icon for Advocate II rankAdvocate II
6 years ago

Divide previous row by next row

Hi Guys, I have the following data set:   Date Keyword Count 01-01-2020 ABCD 5 01-01-2020 DEFG 2 01-01-2020 HIGK 3 01-01-2020 LMNO 0 01-01-2020 PQRS 3 01-01-2020 ...
Need Help
nandukrishnavs's avatar
nandukrishnavs
Icon for Community Champion rankCommunity Champion
6 years ago

PrathSable 

 

Okay Got it.

Val =
VAR nextDate =
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Keyword]
                = EARLIER ( 'Table'[Keyword] )
                && 'Table'[Date]
                    < EARLIER ( 'Table'[Date] )
        ),
        'Table'[Date]
    )
VAR nextDateValue =
    SUMX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Date] = nextDate
                && 'Table'[Keyword]
                    = EARLIER ( 'Table'[Keyword] )
        ),
        'Table'[Count]
    )
RETURN
    DIVIDE (
        nextDateValue,
        [Count],
        BLANK ()
    ) * 'Table'[Actual]



Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

AlB's avatar
AlB
Icon for Community Champion rankCommunity Champion
6 years ago

PrathSable 

Just change the order in the DIVIDE () then. You will have to multiply the result by the "actual" column if that is what you need

Calc Column =
VAR previousDate_ =
    CALCULATE (
        MAX ( Table1[Date] ),
        ALLEXCEPT ( Table1, Table1[Keyword] ),
        Table1[Date] < EARLIER ( Table1[Date] )
    )
VAR previousValue_ =
    CALCULATE (
        DISTINCT ( Table1[Count] ),
        ALLEXCEPT ( Table1, Table1[Keyword] ),
        Table1[Date] = previousDate_
    )
VAR currentValue_ = Table1[Count]
RETURN
    DIVIDE ( previousValue_, currentValue_ )

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs

Cheers 

  • PrathSable's avatar
    PrathSable
    Icon for Advocate II rankAdvocate II
    6 years ago

    Hi AlB ,

     

    This works out , I guess there's something wrong in my data that I need to figure out.

    However, is it possible to create a custom column for same period last year? something like this:

    Dynamic for different Keywords, so that the aggregation remains perfect for me to calculate percentages of change.

     

    FYI: There is data for multiple years, I tried sameperiodlastyear, previousyear; but doesnt work in calculated column 😞

     

    Dynamic for different keywords for different dates

    Appreciate your help in advance

     

    Regards,

    PrathSable

    • AlB's avatar
      AlB
      Icon for Community Champion rankCommunity Champion
      6 years ago

      PrathSable 

      for last year's value:

      Calculated Column =
VAR previousYearDate_ =
    DATE ( YEAR ( Table1[Date] ) - 1, MONTH ( Table1[Date] ), DAY ( Table1[Date] ) )
RETURN
    CALCULATE (
        DISTINCT ( Table1[Count] ),
        ALLEXCEPT ( Table1, Table1[Keyword] ),
        Table1[Date] = previousYearDate_
    )

       

      Please mark the question solved when done and consider giving kudos if posts are helpful.

       

      Contact me privately for support with any larger-scale BI needs

      Cheers 

       

      • PrathSable's avatar
        PrathSable
        Icon for Advocate II rankAdvocate II
        6 years ago

        Hi AlB ,

         

        Thanks for this. One last calculated column and a measure: Running total for dates for Individual keywords.

        I tried creating Running total Quick measure but it doesn't work as desired as it does only dates, as I need to calculate a Running Total custom column or a measure that will bring into consideration the Keywords as well.

        Like Running total for 2020 will be 2018+2019+2020

         

        I used the below: 

         

        Daily Search Volume running total in Date =
        CALCULATE(
            SUM('Master Keywords and Search Volume'[Daily Search Volume]),
            FILTER(
                ALLSELECTED('Master Keywords and Search Volume'[Date]),
                ISONORAFTER('Master Keywords and Search Volume'[Date], MAX('Master Keywords and Search Volume'[Date]), DESC)
            )
        )

        Need to calculate Running Total

        Truly appreciate your help on this.

         

        Regards,

        PrathSable