Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
Let's say i have a table called Orders with number of units sold by resellers in Country by date
Country ResellerID Date Units
3 1 May-17 7
3 1 May-16 4
3 2 May-16 3
2 3 May-16 5
2 3 May-17 8
I have to compare total units sold in specific month basing on a slicer selection to the last year total units in the corresponding month. This is relatively easy and I do this using the formula below
UnitsLastYear = CALCULATE(SUM(Orders[Units]),SAMEPERIODLASTYEAR(Orders[Date]))
So for May-17 chosen on the slicer I have:
This year = 15
Last Year = 12
However there is another condition for calculating last year's value and this is that I should only take Resellers that sold anything in current year's selected month so in this example ResellerID = 2 should be excluded from last year calculation and Last Year should have value of 9
Does anyone can help how to transform this calculation so that it would reflect this condition?
Solved! Go to Solution.
It looks like this one does the trick
= CALCULATE(SUM(Orders[Units]), SAMEPERIODLASTYEAR(Orders[Date]), Orders[ResellerID] IN VALUES(Orders[ResellerID]))
It looks like this one does the trick
= CALCULATE(SUM(Orders[Units]), SAMEPERIODLASTYEAR(Orders[Date]), Orders[ResellerID] IN VALUES(Orders[ResellerID]))
Please give this a shot
UnitsLastYear = CALCULATE ( SUM ( Orders[Units] ), SAMEPERIODLASTYEAR ( Orders[Date] ), EXCEPT ( CALCULATETABLE ( VALUES ( Orders[ResellerID ] ), FILTER ( Orders, YEAR ( Orders[Date] ) = YEAR ( SELECTEDVALUE ( Orders[Date] ) ) ) ), CALCULATETABLE ( VALUES ( Orders[ResellerID ] ), FILTER ( Orders, YEAR ( Orders[Date] ) = YEAR ( SELECTEDVALUE ( Orders[Date] ) ) - 1 ) ) ) )