Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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 ) ) ) )
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
90 | |
82 | |
62 | |
61 | |
58 |
User | Count |
---|---|
160 | |
114 | |
100 | |
74 | |
65 |