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
PrathSable's avatar
PrathSable
Icon for Advocate II rankAdvocate II
6 years ago

Hi amitchandak : This is close: But what I want to actually achieve is:

 
 

Actual values to find

For our formula for 02-01-2020 we get the divide % for 01-02-2020. Any suggestions on how to achieve the above?

 

Regards,

PrathSable

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

PrathSable 

 

Try this calculated column

Percentage = 
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]*'Table'[Actual],BLANK())



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

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

    nandukrishnavs :

     

    I think I confused you, I do not want the percentage.

     I just want to divide earlier record with latest one & then that would need to be multiplied with a corresponding value.

     

    Want to achieve this??

     

    Suggestions?

     

    Regards,

    PrathSable

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi PrathSable ,

       

      Incase you want to use Calculated Columns.

       

       

       

       

      Previous Date =
      CALCULATE (
      MAX ( 'Table'[Date] ), FILTER(
      ALLEXCEPT ( 'Table','Table'[Keyword] ),
      'Table'[Date] < EARLIER( ( 'Table'[Date] ))
      )
      )
       
      Previous Value =
      CALCULATE (
      MAX('Table'[Count]) , FILTER(
      ALLEXCEPT ( 'Table', 'Table'[Keyword] ),
      'Table'[Date] = EARLIER('Table'[Previous Date])
      ))
       
      Divide Val = DIVIDE ('Table'[Previous Value],'Table'[Count])
       
      Final Val = 'Table'[Divide Val] * 'Table'[Actual]
       
       

      Regards,
      Harsh Nathani

      Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

    • 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