@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 @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 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
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 ;)
@@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!
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
@@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
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)
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.
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
El uso de variables para este tipo de calculo es lo mejor que existe; Gracias Ruth como siempre muy claro y preciso.
Thank you for this video it helpt my so much.
This is excellent, thank you for sharing. How would I tack on % change SPPY to this?
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
This is exactly what I've been looking for, thank you :)
🥳
Thanks a lot !! , i was looking for this formula .. appreciate you help very much :)
Thank you Ton!
Thank you, this is what i needed. Helped me a lot.
@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
is that possible to make the today as date i could pick ??
That's exactly what I was looking for, thank you for sharing!
Fab! 🥳
very nice solution.
Hi Ruth..Great work! Could u please do a video on incremental refresh with excel files or any sort of flat files.
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
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?
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
Love it. Thank you Ruth!
You are so welcome!
Love this
How do you calculate based on the most current period or month instead of Today's date?
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
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!
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
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
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?
Buenas noches, se podría hacer pero pudiendo tener un segmentador de mes y año? Justo necesito comparar el 2019 VS 2021
Very clever.
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)
Why 2021 YTD SPPY returning same value as YTD 2021?
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!!
🥳🥳🥳
Doesn't the time intelligence function SAMEPERIODLASTYEAR do the same thing?
But for one year only right?
Sure wish you would add date slicers. These functions do not work with date slicers for the most part. Sad.
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