DAX Fridays!
Vložit
- čas přidán 1. 11. 2021
- In todays video I will show you how to calculate year to date for the same period previous years, all in one measure!
Here you can download all the pbix files: curbal.com/donwload-center
SUBSCRIBE to learn more about Power and Excel BI!
/ @curbalen
Our PLAYLISTS:
- Join our DAX Fridays! Series: goo.gl/FtUWUX
- Power BI dashboards for beginners: goo.gl/9YzyDP
- Power BI Tips & Tricks: goo.gl/H6kUbP
- Power Bi and Google Analytics: goo.gl/ZNsY8l
☼☼☼☼☼☼☼☼☼☼
POWER BI COURSES:
Want to learn Power BI? How about you take one of our courses? Here you can find the available courses:
curbal.com/courses-overview
☼☼☼☼☼☼☼☼☼☼
ABOUT CURBAL:
Website: www.curbal.com
Contact us: www.curbal.com/contact
▼▼▼▼▼▼▼▼▼▼
If you feel that any of the videos, downloads, blog posts that I have created have been useful to you and you want to help me keep on going, here you can do a small donation to support my work and keep the channel running:
curbal.com/product/sponsor-me
Many thanks in advance!
▲▲▲▲▲▲▲▲▲▲
************
What gear do I use to make my videos and run my business? Below you will find a list of most of my gear. The links on the store are affiliate links, meaning if you buy something from them, amazon will give a small commission and you will be supporting my channel indirectly. Thanks in advance!
www.amazon.com/shop/curbal
************
QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
Linkedin ► goo.gl/3VW6Ky
Twitter ► @curbalen, @ruthpozuelo
Facebook ► goo.gl/bME2sB
#CURBAL #SUBSCRIBE
El uso de variables para este tipo de calculo es lo mejor que existe; Gracias Ruth como siempre muy claro y preciso.
This is exactly what I've been looking for, thank you :)
🥳
That's exactly what I was looking for, thank you for sharing!
Fab! 🥳
Love it. Thank you Ruth!
You are so welcome!
Thanks a lot !! , i was looking for this formula .. appreciate you help very much :)
Thank you Ton!
Great measure but this doesnt quite work for me. If I apply the day
Yes.... seems that this shows for months only untill specified day... So for somereason DAX behaves wrong with &&/AND notification
Thank you, this is what i needed. Helped me a lot.
Love this
Hi Ruth..Great work! Could u please do a video on incremental refresh with excel files or any sort of flat files.
very nice solution.
This is excellent, thank you for sharing. How would I tack on % change SPPY to this?
Very clever.
@Curbal, Thanks for this! What if we have to pick the 'to-date' from a relative filter instead of today? I tried the same approach with max(selected date) instead of today but it does not work.
Whilst this comment was a while ago, I have just come across this problem and think i've found a solution - may help someone else coming across the same problem:
VAR _Month = MONTH(calculate(max('FACTtablename'[Date]),removefilters('Date')))
Hi,
I have one question. Your total should be sum of 2019+2020+2021 which is not tha case. Can you pls explain
I have a date range like 10/25/2021 to 10/29/2021
In which next two days are holidays 10/30/2021 & 10/31/2021 & not shown above said range.
If I calculate averagex on 11/01/2021 for 2 days
With the help of dateadd then it shown nothing because averagex count two holidays which is not shown in above said range.
Please reply maa'am
is that possible to make the today as date i could pick ??
I don't have that Calendar table, it does not appear, I do have a Date table tho, but it does not appear as it shows on the video, any advice?
How do you calculate based on the most current period or month instead of Today's date?
Buenas noches, se podría hacer pero pudiendo tener un segmentador de mes y año? Justo necesito comparar el 2019 VS 2021
Hi @curbal. This is great but I cannot get this to work for YTD fiscal Year. It doesn't give me the YTD for the last few years... i.e it doesn't give me July to Oct FY 21 - July to Oct FY 22 - Jul to Oct FY 23....
Would you be able to guide me how to get that please?
Thanks
Hi, I'm having a problem, when I use sameperiodlastyear or dateadd functions it goes completely blank, but it's only when I use time intelligence formulas, and I don't know what else to do
I would love to see a video if you could do day to day comparison to prior year. but if for example 1/2 /23 is Monday compare it to 1/3/22 Monday, because (1/2/22 is a Sunday)
Apologize. I am a newbie. I am totally confused of the logic. Should not the YTD 2020 number be displayed as YTD SPPYs 2021 number?
Why 2021 YTD SPPY returning same value as YTD 2021?
Dont use BI can this be done in excel power query? Thanks
Yes, but it can get very slow. You can use DAX in excel too
Wonder if there is a way to make this measure more dynamic? So this is year to date for each year, to today. Is there a way for the end user to choose a date on a slicer and for the measure to dynamically change with it? Eg select november 8th, measure shows year to that date for each year
Maybe? Check tomorrow’s video, a combo of both might do it
@@CurbalEN thank you, will do. Much appreciate your work! Really helpful!
@@CurbalEN fantastic video today regarding measures within date ranges, unfortunately unable to blend it with the measure in this video due to the variables for month and day being related to today, any suggestions to get this to work? Thanks again for your helpful videos
You can have the user move a date slicer and pick the dates from there?
@@CurbalEN I will give it another go! Thank you
Think that measure has a issue. Since
Me too - my immediate thought. Unless Calendar function has some powerful logic? Ruth, can you help us understand?
I think it is alright. The only problem you could get is when one of the previous years was one where February had 29 days and the actual february only counts 28... but this can be tolerated ;)
@@cg24356 I've tested in Power BI and you only get 1st and 2nd populated because today is the 2nd
@@jakeduddy3257 @Jake Duddy i see your concern. And now I'm also a bit confused. Still she shows that it works and I think it must have something to do with the ytd measure (which is calculated with the function "totalytd"). The essence of this function is to sum up from 1st day of a year up to today's date/certain date. She is NOT using the sales measure. And probably that is why it still works.
A bit late to the discussion, but I believe it works because of what Clemens mention, the YTD measure instead of the sales measure. Test it and let me know if it doesnt work for you!
Doesn't the time intelligence function SAMEPERIODLASTYEAR do the same thing?
But for one year only right?
Hi, I purchased from your website for some courses and I haven't received anything back. I have sent two follow up messages. My card was charged and I still cannot access the courses. Please help.
@@CurbalEN Thank you! Just sent the email. :)
And I found your order and gave you access manually, please let me know if it is not working.
Apologies :(
@@CurbalEN I have access now!! THANK YOU SO MUCH! Can't wait to keep learning from you!!
🥳🥳🥳
I dont know why this is giving me whole month previous ytd rather than until 27
OK... so this works only for the years but not for the months if you'd need to drill from year level to month level. Seems that dax engine cuts for all months from the specified day and not only after specified month. I tried sqlBi's solution to tag day as past=true/false but could it be theres a major bug how M-language works?? Tried this
=if
Date.Month(DateTime.LocalNow()) >= [MonthID]
then
if
Date.Day(DateTime.LocalNow()) > [DayID]
then
true
else
false
else
false
but also with this M-engine tags all days on every month falsely and not just after the specified month. In this going to outher if-clause should only happen forexample now every year september (9th month) and after but with this it tags falsely for every month...
Could it be that theres a bit of an bug on most basic stuff?
Just realized that in curbals video this bug doesnt exist!!! Now I also tried making a table with filter... and in there it also cuts every month from the specified day. Not just after specified month. This is strange
ok... so got this working on dax calculated col and M-language. Example in on M
if
Date.Month(DateTime.LocalNow()) >= [MonthID]
then
if
Date.Day(DateTime.LocalNow()) > [DayID]
or
Date.Month(DateTime.LocalNow()) > [MonthID]
then
true
else
false
else
false
after this am having col with true/false value showing is the time past or present on every year backwards. So... abit odd behaviour from powerBIs side but like this you are also able to drill on month level