Forum Discussion
Find difference between two values in filtered matrixes
I have user defined filters on two differnt matrixes that I need to create a measure that I can show the difference between values in the two tables. Example matrix one filtered for 2019 total sales by department and region Matrix 2 2020 total sales by department and region.
5 Replies
I hope this helps. I have three user defined matrixs using the same data but with diffetnt filters. I need a way to create a measure for the variances between the selection ie. variance betwen table 1 and 2 amount paid or variance between 2 and 3 amount paid etc
jamesrcleveland , In case they are from the same table you can measure like this using date table and time intelligence
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
//Only year vs Year, not a level below
This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))
rolling = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]>=max('Date'[Year])-2 && 'Date'[Year]<=max('Date'[Year])) )diff = [This Year]-[Last Year ]
diff % = divide([This Year]-[Last Year ],[Last Year ])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.
Not exactly the data is from the same tables but the values come from tow different Matrix which can each be filterd by differnt values
Hi jamesrcleveland ,
It is very difficult to analyze without looking at the data and just by imagining. See if you can share the dummy pbix via cloud service like onedrive for business?
Please remove any sensitive data before uploading.
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
