Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Can anyone help with how to display same period last year calculation? ie, I want to show sales this year up to today, and the comparative sales for the same period last year. I am not very good with DAX, so need something simple. To view comparative periods seems like a basic requirement, but I cannot see anything simple to do it. I have a sales table, and have created a separate date table. Any help greatly appreciated!
Solved! Go to Solution.
Hey,
creating a separate date column is a great start. The most simple calculation for the Year-To-Date pattern can be accomplished using the build-in function TOTALYTD().
This statement creates a Year-To-Date calculation
Year-To-Date Value = TOTALYTD(SUM('Simple Facts'[Value]),'Calendar'[Date])
And this statement does the same for the previous year:
Year-To-Date Value LY = TOTALYTD( SUM('Simple Facts'[Value]) ,DATEADD('Calendar'[Date] ,-12 ,MONTH ) )
The workings of these both statements are depicted here:
If these statements do not fit your needs, than it's most probable that the following site will answer your questions:
https://www.daxpatterns.com/time-patterns/
Hopefully this gets you started
Regards
Tom
Hey,
creating a separate date column is a great start. The most simple calculation for the Year-To-Date pattern can be accomplished using the build-in function TOTALYTD().
This statement creates a Year-To-Date calculation
Year-To-Date Value = TOTALYTD(SUM('Simple Facts'[Value]),'Calendar'[Date])
And this statement does the same for the previous year:
Year-To-Date Value LY = TOTALYTD( SUM('Simple Facts'[Value]) ,DATEADD('Calendar'[Date] ,-12 ,MONTH ) )
The workings of these both statements are depicted here:
If these statements do not fit your needs, than it's most probable that the following site will answer your questions:
https://www.daxpatterns.com/time-patterns/
Hopefully this gets you started
Regards
Tom
Thanks very much Tom, that gives me a good starting point. Is there a way to automatically recognise today's date and show last years sales up to that date only?
Regards, Sc
Glad,
that works for you, at least gives you start.
The function TODAY() may provide a solution to your latest question.
Here is an example, that may provide what you are looking for. Please be aware, that the formula below considers TODAY and the YEAR(TODAY()), but the statement may be exceuted in a context where you may not exptect it.
Year-To-Date Today = var dateToday = TODAY() return CALCULATE(SUM('Simple Facts'[Value]) ,FILTER(ALL('Calendar'[Date]) ,'Calendar'[Date] <= dateToday && YEAR('Calendar'[Date]) = YEAR(dateToday) ) )
If you think your question is answered, please mark a post as answer, this may help others.
Regards
Tom
Can you explain what happens for the below?
"the statement may be exceuted in a context where you may not exptect it."
Could you post the example formula with TODAY function that you mentioned in your post, it was missing from your post.
Thanks.
silly me, see my post above
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
101 | |
73 | |
70 | |
47 |
User | Count |
---|---|
145 | |
106 | |
105 | |
90 | |
65 |