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,
Apologies as I know this has been asked and answered numerous times before.
I'm trying to get the sales for LY 01/01/2022 to todays date but unsure on how to go about it.
I tried to use CALCULATE(SUM(Transactions[Sales]),SAMEPERIODLASTYEAR(Calendar[Date])) but this just pulls up the full year sales of previous years.
Any help would be greatly appreciated.
Thanks,
Solved! Go to Solution.
hi @Anonymous
you need some code to reflect YTD, try like:
CALCULATE(
SUM(Transactions[Sales]),
DATESYTD(SAMEPERIODLASTYEAR(Calendar[Date]))
)
H
hi @Anonymous
you need some code to reflect YTD, try like:
CALCULATE(
SUM(Transactions[Sales]),
DATESYTD(SAMEPERIODLASTYEAR(Calendar[Date]))
)
All of the replies were giving me full years value rather than same period for some reason.
I updated my calendar table which was 2020 - 2029 to 2020 - todays date and this one appeared to be better suited for my needs but still with a slight issue, it rolls up january and full month of february for the previous year rather than 01/01 - 20/02
Solved it by marking as date table! Thanks 🙂
Did you test my formula, I specifically made it same period?
FILTER(ALL(Calendar),YEAR(Calendar[Date]) = todayYear -1
&& Month(Calendar[Date]) <= todayMonth
&& Day(Calendar[Date]) <= todayDay
Yes, it's pulling up FY, unsure why.
Can you show the relationships between the table and the date dimension? It seems you might have a problem there.
I am oversimplify so you can follow the DAX and test, but you need to start with wrting some variables.
PreviousYearTodate=
VAR todayYear = Year(Today())
VAR todayMonth= Month(Today())
VAR todayDay = Day(Today())
RETURN CALCULATE(SUM(Transactions[Sales]),
FILTER(ALL(Calendar),YEAR(Calendar[Date]) = todayYear -1
&& Month(Calendar[Date]) <= todayMonth
&& Day(Calendar[Date]) <= todayDay )
)
Hi,
Here is one way to do this:
I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!
My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/
Proud to be a Super User!
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 |
---|---|
22 | |
21 | |
20 | |
14 | |
11 |
User | Count |
---|---|
43 | |
34 | |
25 | |
23 | |
23 |