Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
Hi @rbustamante ,
Based on the data you provided here:
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,
Hi @rbustamante ,
Based on the data you provided here:
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,
Sorry it is not posible to share the pbix file
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
@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.
Thank for the response but not works. It is not posible to know the difference between months.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
23 | |
21 | |
15 | |
15 |
User | Count |
---|---|
45 | |
31 | |
30 | |
18 | |
17 |