Forum Discussion
Divide previous row by next row
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
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 🙂
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
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
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
