Forum Discussion

rbustamante's avatar
rbustamante
Helper I
5 years ago
Solved

Calculate difference between columns in matrix visual

I have the following matrix visual:

I need two things when the user clicks on the "Date slicer":

1) dynamically calculate the difference between the ammount of two dates. Ammount of 31/12/2020 less ammount of 30/04/2020.

2) dynamically calculate the difference between the ammount of the last date 31/12/2020 and the ammount of the first date 31/12/2019

6 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    rbustamante , With help from date table , First seem diff day vs last

    Last Day Non Continuous = CALCULATE([sales],filter(ALLSELECTED('Date'),'Date'[Date] =MAXX(FILTER(ALLSELECTED('Date'),'Date'[Date]<max('Date'[Date])),'Date'[Date])))

     

    This Day = CALCULATE([sales], FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])))

     

    Diff = [This Day] - [Last Day Non Continuous]

     

    Between first and last date

     

    new measure =
    var _min = minx(allselected('Date'), 'Date'[Date])
    var _max = maxx(allselected('Date'), 'Date'[Date])
    return
    calculate([measure], filter( 'Date', 'Date'[Date] =_max )) -calculate([measure], filter( 'Date', 'Date'[Date] =_min )))

     

    or

     

    new measure =
    var _min = minx(allselected('Date'), 'Date'[Date])
    var _max = maxx(allselected('Date'), 'Date'[Date])
    return
    calculate([measure], filter( Table, Table[Date] =_max )) -calculate([measure], filter( Table, Table[Date] =_min )))

     

    Use date Table

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

     

    • rbustamante's avatar
      rbustamante
      Helper I
      5 years ago

      Thank for the response but not works. It is not posible to know the difference between months.

    • rbustamante's avatar
      rbustamante
      Helper I
      5 years ago

      Thanks for your reply.

      I try to explain better:

      In this case I choose 3 dates and I need to know de difference between Ammount 31/03/2020 and 31/12/2019 and the difference between 30/04/2020 and 31/03/2020.

      If I choose different dates I need to know the diferences between amounts at these dates.

      I share the file

       

       

  • Payeras_BI's avatar
    Payeras_BI
    Solution Sage
    5 years ago

    Hi rbustamante ,

     

    Based on the data you provided here: 

     

    https://community.powerbi.com/t5/DAX-Commands-and-Tips/How-to-calculate-measure-based-on-previous-date/m-p/1589940 

     

     

    mDifference = 
VAR _CurrentDate =
    SELECTEDVALUE ( 'Products (2)'[Date])
VAR _PreviousDate =
    CALCULATE (
        MAX ( 'Products (2)'[Date] ),
        ALLSELECTED ( 'Products (2)'[Date] ),
        KEEPFILTERS ( 'Products (2)'[Date] < _CurrentDate )
    )
VAR _ThisMonth =
    CALCULATE ( SUM ( 'Products (2)'[Amount] ) )
VAR _PreviousMonth =
    CALCULATE (
        SUM ( 'Products (2)'[Amount] ),
        'Products (2)'[Date] = _PreviousDate
    )
RETURN
    _ThisMonth - _PreviousMonth

     

     

    Regards,