Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi Folks, I need asssitance to acheieve below scenario.
Trying to calculate the sales difference for year on year and for each product. By default, all years will be dispayed, the main cache is if the user selects different year how to calculated. For example: if user selectes 2019, 2021, 2023.
Data:
Expected result:
Solved! Go to Solution.
Hi, I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file.
I hope the below can provide some ideas on how to create a solution for your semantic model.
compare to the previous within the slicer selection: =
VAR _currentselectsales =
SUM ( Sales[Sales] )
VAR _currentselectyear =
MAX ( 'Year'[Year] )
VAR _prevselectyear =
MAXX (
FILTER ( ALLSELECTED ( 'Year' ), 'Year'[Year] < _currentselectyear ),
'Year'[Year]
)
VAR _prevselectsales =
CALCULATE ( SUM ( Sales[Sales] ), 'Year'[Year] = _prevselectyear )
RETURN
IF ( NOT ISBLANK ( _prevselectsales ), _currentselectsales - _prevselectsales )
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi, I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file.
I hope the below can provide some ideas on how to create a solution for your semantic model.
compare to the previous within the slicer selection: =
VAR _currentselectsales =
SUM ( Sales[Sales] )
VAR _currentselectyear =
MAX ( 'Year'[Year] )
VAR _prevselectyear =
MAXX (
FILTER ( ALLSELECTED ( 'Year' ), 'Year'[Year] < _currentselectyear ),
'Year'[Year]
)
VAR _prevselectsales =
CALCULATE ( SUM ( Sales[Sales] ), 'Year'[Year] = _prevselectyear )
RETURN
IF ( NOT ISBLANK ( _prevselectsales ), _currentselectsales - _prevselectsales )
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
@Jihwan_Kim One more quick question. How to apply filter>70, on top of the above result?
Perfect
Hi, I am not sure if I understood your question correctly, but do you mean something like below?
compare to the previous within the slicer selection: =
VAR _currentselectsales =
SUM ( Sales[Sales] )
VAR _currentselectyear =
MAX ( 'Year'[Year] )
VAR _prevselectyear =
MAXX (
FILTER ( ALLSELECTED ( 'Year' ), 'Year'[Year] < _currentselectyear ),
'Year'[Year]
)
VAR _prevselectsales =
CALCULATE ( SUM ( Sales[Sales] ), 'Year'[Year] = _prevselectyear )
RETURN
IF (
NOT ISBLANK ( _prevselectsales )
&& _currentselectsales - _prevselectsales > 70,
_currentselectsales - _prevselectsales
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
I'm trying to bring in the same functionality and trying to use this same DAX but it's throwing an error.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
85 | |
82 | |
67 | |
49 |
User | Count |
---|---|
137 | |
112 | |
101 | |
66 | |
64 |