topic Re: Calculate difference between columns in matrix visual in DAX Commands and Tips

Calculate difference between columns in matrix visual

rbustamante — Thu, 07 Jan 2021 00:01:53 GMT

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

Re: Calculate difference between columns in matrix visual

amitchandak — Thu, 07 Jan 2021 01:42:08 GMT

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 )))

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.

Re: Calculate difference between columns in matrix visual

rbustamante — Thu, 07 Jan 2021 14:24:01 GMT

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

Re: Calculate difference between columns in matrix visual

AntrikshSharma — Thu, 07 Jan 2021 15:45:14 GMT

rbustamante Should be pretty easy, share a PBIX file with sample data.

Re: Calculate difference between columns in matrix visual

rbustamante — Fri, 08 Jan 2021 00:44:16 GMT

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

Re: Calculate difference between columns in matrix visual

rbustamante — Fri, 08 Jan 2021 00:47:25 GMT

Sorry it is not posible to share the pbix file

Re: Calculate difference between columns in matrix visual

Payeras_BI — Thu, 14 Jan 2021 16:32:52 GMT

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,