Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Folks,
I use the following 2 formulae to get the sales figure up to the same day in the previous year - I now need to get the same figure for 2 years ago - can anyone help ??
Many thanks
Richard
Solved! Go to Solution.
Sorry found a typo, this should work (it did on my dataset atleast):
RunningTotal2LY =
VAR curYrMinDate = MIN( 'Time'[PK_Date])
VAR curYrMaxDate = MAX( 'Time'[PK_Date] )
VAR minDate = DATE( (YEAR( curYrMinDate ) -2 ),MONTH(curYrMinDate), DAY(curYrMinDate))
VAR maxDate = DATE( (YEAR( curYrMaxDate ) - 2 ), MONTH(curYrMaxDate), DAY(curYrMaxDate))
RETURN
CALCULATE(
[Total Sales],
FILTER(
ALLSELECTED('Time'[PK_Date]),
'Time'[PK_Date] > minDate && 'Time'[PK_Date] < maxDate
)
)
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂
Proud to be a Super User!
Hi @Richard76 ,
This should do the trick I think (haven't tested it though, hard to create a dataset quickly for this one, apologies);
RunningTotal2LY =
VAR curYrMinDate = MIN( 'Time'[PK_Date] )
VAR curYrMaxDate = MAX( 'Time'[PK_Date] )
VAR minDate = DATE( YEAR( curYrMinDate ) - 2, MONTH( curYrMinDate ), DAY( curYrMinDate ))
VAR maxDate = DATE( YEAR( curYrMaxDate ) - 2, MONTH( curYrMaxDate ), DAY(curYrMaxDate ))
CALCULATE(
SUM([Total Sales],
FILTER(
ALLSELECTED('Time'[PK_Date]),
'Time'[PK_Date] > minDate && 'Time'[PK_Date] < maxDate
)
)
Let me know if that works for you!
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂
Proud to be a Super User!
Wow impressive ! I've tried copying in but just says 'syntax is incorrect' . Would you be able to talk me through what it is doing so I can try and self diagnose. I've never used VAR before in DAX so not sure what all the formulae is doing.
Thanks for your help .
Richard
Proud to be a Super User!
Still not working but could be something to do with my data .
Will let you know .
R
Sorry found a typo, this should work (it did on my dataset atleast):
RunningTotal2LY =
VAR curYrMinDate = MIN( 'Time'[PK_Date])
VAR curYrMaxDate = MAX( 'Time'[PK_Date] )
VAR minDate = DATE( (YEAR( curYrMinDate ) -2 ),MONTH(curYrMinDate), DAY(curYrMinDate))
VAR maxDate = DATE( (YEAR( curYrMaxDate ) - 2 ), MONTH(curYrMaxDate), DAY(curYrMaxDate))
RETURN
CALCULATE(
[Total Sales],
FILTER(
ALLSELECTED('Time'[PK_Date]),
'Time'[PK_Date] > minDate && 'Time'[PK_Date] < maxDate
)
)
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂
Proud to be a Super User!
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |