Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Calculating the difference and % difference between 2 columns

Hello, 

 

I am looking to calculate the numerical difference (in one column) and % difference (another column) between the last two dates for each row: 31st July and 17th August. The values in this table are from one measure, filtered by date and Financial Partner.

 

Thank you. 

 

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , Create a new table with distinct dates from this table

     

    dates = distinct(Fin[date])

     

    Then add new rank column

    rank = rankx(dates, [Date],,asc,dense)

     

    Join this with date of you date table

     

    and create measure like

    This Date= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Dates'),'Dates'[Rank]=max('Dates'[Rank])))
    Last Date= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Dates'),'Dates'[Rank]=max('Dates'[Rank])-1))

     

    Now you can have diff = [This Date] - [Last Date]

     

    diff % = divide([This Date] - [Last Date], [last Date])

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hello Amit, 

    I have done the measures, many thanks. 

     

    However, although the rank is correct - it is only calculating for previous day. How do I calculate it so that it is always max date, and then last day of the previous month i.e 31st July?