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