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
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

PrathSable , Try as new column


Last Date = maxx(filter(table,[date]<earlier([date]) && [keyword] =earlier([keyword])),[date])
Ration with Last = divide([Count],maxx(filter(table,[date]=earlier([Last Date ]) && [keyword] =earlier([keyword])),[Count]))

 

In a measure this how you get last value with help from date table


Last Day Non Continous = CALCULATE(sum('Table'[Count]),filter(all('Date'),'Date'[Date] =MAXX(FILTER(all('Date'),'Date'[Date]<max('Date'[Date])),Table['Date'])))
Day behind Sales = CALCULATE(SUM(Table[Count]),dateadd('Date'[Date],-1,Day))

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi PrathSable ,

     

    You cna use this measure

     

    Divided Value =
    VAR previousDate_ =
    CALCULATE (
    MAX ( 'Table'[Date] ), FILTER(
    ALLEXCEPT ( 'Table','Table'[Keyword] ),
    'Table'[Date] < MAX ( 'Table'[Date] ))
    )
    VAR previousValue_ =
    CALCULATE (
    MAX( 'Table'[Count] ), FILTER(
    ALLEXCEPT ( 'Table', 'Table'[Keyword] ),
    'Table'[Date] = previousDate_
    ))
    RETURN
    DIVIDE ( previousValue_, MAX('Table'[Count])) * MAX('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 

     

    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)