Power BI Show Year-to-Date (YTD) Across Multiple Years
Вставка
- Опубліковано 15 лип 2024
- Compare Year-to-Date (YTD) Performance across Multiple Years!
👉 Download Power BI File in Video at: web.learnpowerbi.com/download/
This is not your average YTD Measure, this requires something extra special. And we show not one, but two different ways to solve this puzzle.
Links mentioned in the video:
→Power BI Ultimate Calendar Table (Free Download):
• • Power BI: The Ultimate...
→ Download Power BI Pro Files:
• www.LearnPowerBI.com/download
→ Ultimate Power BI Calendar Playlist:
• • Power BI: The Ultimate...
================================
👉 FREE Power BI Step-by-Step Tutorial web.learnpowerbi.com/tutorial
👉 Download Accompanying PBIX Files for Video at web.learnpowerbi.com/download/
✅ Subscribe to always get my latest videos: goo.gl/P79Hcr
🔴 Join me Fridays for Talk Power BI LIVE ua-cam.com/users/PowerBIProlive
👪 Join our Talk Power BI Insider's Club www.TalkPowerBI.com
===Most Popular Playlists===
• Power BI Beginner Tutorial: goo.gl/KRRkPG
• Ultimate Calendar: goo.gl/pyki4K
• Power BI Data Modeling and more: goo.gl/WNtJFv
⚡Power On!⚡
-Avi Singh
www.LearnPowerBI.com
www.AviSing.com
#PowerBI #PowerBIHowTo #PowerBIPro
Today at 10am i starting googling how to calculate what you just calculated here... it's not 5pm ... i just found this video. this solution is amazing! very very very well done!
The more i watch these videos the more i understand DAX and how to use it inside power bi. I struggled on my on for months trying to learn how to use power bi, but in the last couple weeks i feel like i have made real progress using the methods you talk about in your videos. Thanks for the help and keep up the awesome work
Hi Ian Voll.
Thank you for your good words. I'm so glad that you are finding my videos helpful. Do update me with your progress.
⚡Power On!⚡ / Avi
★ Power BI Complete Tutorial: ua-cam.com/video/AGrl-H87pRU/v-deo.html ★
★ Subscribe and Join me LIVE for Talk Power BI Fridays ua-cam.com/users/PowerBIProlive ★
I've just found you channel as I do build up some reports for a customer and learning how to use Power BI … and what can I say .. just wow, your videos are amazing and I already love your ultimate calender after 10 minutes - thank you so much!
Hi Andrea Waldherr-Floquet,
Glad to help Andrea and thank you for the compliment. I appreciate it.
⚡Power On!⚡ / Avi
★ Get Your Questions Answered LIVE on TalkPowerBI Fridays www.learnpowerbi.com/talkpowerbi ★
Thanks Avi for sharing so much with us, very helpful!!
haha I was just solving this very same problem ....yesterday!! I ended up using a parameter to identify the last month of data for actuals since we have forecast and therefore we have data all the way to 2020... definitely a workaround!...I'm going to finish the video to see what genius idea Avi came up with! Thank you for sharing my friend!
Yup, there are always a few different ways to solve the problem. You did get it working, that's awesome. We can always find better ways or new ways and compare them. In fact that can be quite fun, comparing the Pros & Cons of different approaches.
⚡Power On!⚡ / Avi
★ Power BI Beginner Tutorial: ua-cam.com/play/PL7GQQXV5Z8ef2SjkDpLnvsz7TAQjlzlpO.html ★
★ Subscribe and Join me LIVE for Talk Power BI Fridays ua-cam.com/users/PowerBIProlive ★
Your explanation of applying the CALCULATE filter functions as so intuitive, love it.
Glad you think so!
⚡Power On!⚡ / Avi
★ Subscribe and Join me LIVE for Talk Power BI Fridays ua-cam.com/users/PowerBIProlive ★
Hi Avi, You made my day and this is really super helpful to fix my scenario. Actually I was looking to fix since today's to calculate the Current Year YTD & Previous Year YTD in the same measure. Really you are awesome & great ! Many Thanks.....
CurMonthOffset ... wow its mind blowing !! I can do somany thing with this then. Thank you for the video (y)
Fantastic video Avi. I believe the best measure is the simpler solution and this one is the proof of it. Thanks a lot
Fantastic as always, Avi! I would love to calculate the variance % between the two periods but since they are using the same measure I'm not sure how to do this. Any suggestions? Thanks in advance and for all you do!
Awesome video to address the problem area. It's a challenge to compare partial year result with prior year (full year). The techniques shared in this video is a perfect antidote. Thank you for sharing your knowledge!
Thanks for watching
Un grand merci, c'est exactement ce que je cherchais à calculer.
So powefull!!
Just what I needed.
You made my day.
Thanks!
You're so welcome!
Awesome solution Avi, thank you very much!!
Hey, big thank you for your note. This is what keeps me going.
⚡Power On!⚡ / Avi
★ Subscribe and Join me LIVE for Talk Power BI Fridays ua-cam.com/users/PowerBIProlive ★
Thanks Avi. Good video. The concept of current month offset is really important!
Glad to know you liked it! Make sure to join me live on TalkPowerBI Fridays ua-cam.com/users/powerbiprolive
Hi Avi, as a new Power BI user I am finding your ultimate calendar and tutorials fantastic. Unfortunately our ERP system uses an accounting calendar which works on a 4,4,5 basis for the periods. I have a period table and i can link the period into the calendar but i am unable (to work it out!) to use the very useful CurMonthOffset as this is based around the calendar months and not the periods. I am assuming therefore that i would need to create an accounting period offset, to work out same period last year based upon accounting period and not date.
Been looking for a video like this for sooooo long! Thank you brother!
Avi - great video - Thanks! I've recently been tasked with calculating a fiscal ytd running total. You probably already have a video that deals with that. I just need to know which one it is. Thanks!
Avi, at 12:23 you asked why you needed to use a variable. I'm new to variables but have been finding them useful. If you didn't use a VAR in your measure, you would have been using another measure (maxmonth) in the filter context of the main measure (YTD sales). You can't use a measure in the filter context of another measure bc measures themselves have filter context and DAX requires table columns or constants in the filter parameter of Calculate. The error you would get is: "A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed." So to fix this you can assign a measure value into a variable first and then you can use the variable as a filter in the Calculate.
I appreciate this reply because I got the same error message as I am trying to do this in Excel Power Query. Can you please share a screenshot of how you fixed this in yours?
@@rachelwalden4323 On the last line, you probably used the 'maxmonth' as a measure instead of a variable, so just make sure that after writing down maxmonth, you chose the correct one.
Sales across prior year = var MaxMonth = [MaxMonth]
return CALCULATE([Sales],'Calendar'[MonthNum] This maxmonth here has to be a variable)
@@naea07 Thanks for your formula. I was receiving the same error.Avi -Thanks for your Bundle of info.
Thank you for posting this! I’ve just started using Power BI myself (started a new job a couple months ago and have been using it extensively) and I was scrolling through the comments trying to figure out why a variable is required.
I feel like I still don’t quite understand why you HAVE to use a variable. Hypothetically you COULD use a measure as a filter so long as you only used it tables where the filter only evaluates to a single value, right? And Power BI just has a blanket policy of not allowing measures to be used as filters to avoid confusion, since if it was allowed measures created that way may work correctly in some tables but break in others?
Hopefully that makes sense. Anyway, thanks again for the insightful post.
Thank you for the awesome video. Quick question.. my [Sales] column is a Numeric Calculated Column and not a Measure. The CALCULATE() function doesn’t work for me. Any idea what I could do to fix this?
Hello Avi - I loved your informative video! If you don't mind me asking, I have a question for you. How do I update the measure named "Flag_YTD" in your file's "Calendar" table? It currently covers ytd through current month, however I need YTD thru last completed month. I believe the measure is somehow protected and won't display the underlying formula. Please advise. Thank you so much.
Hi, Thanks for great video! How can I adjust the formula to calculate running YTD on a monthly level, the formula gives me correct YTD on total Year, but on a monthly level I would like to see the running total, not the amount per month. Appreciate any help! Thanks!
Thank you for this! It's great. How would you adjust the YTD measure to show this:
If the current year is selected in the filter slicer with previous years, show YTD totals for all years, if the current year is not selected in the filter slicer (only selecting previous years), show the total sales for the entire year (not YTD).
Thanks!
Thank you Avi, you are the best, your tutorial has solved my headache
You are most welcome
Please help, I want to YTD and MTD in matrix based on the document type totals namely - net sales, order entry and order in hand. But order in hand we have to calculate. Order in hand = order entry - net sales. Order entry and net sales directly available from the table.
Hi Singh. Okay I am doing this analysis that takes the average of last year but to get the values for Jan, Feb, March, ... this year; i have to multiply by number of the current month say 1, 2, 3,.... for Jan, Feb,..respectively. This means the date by which I multiply is the exact number of month of the current month. Any good Idea will be appreciated as I have tried all options open to me and nothing seems to work.
God bless your work )) 🙏
Thank you 🙌
Thanks for sharing. This is very helpful and provides excellent direction. I will definitely utilize the calendar in my dashboards. However, where is the latest Ultimate Calendar as the available one doesn't seem to have all the filters MTD Flag, QTD Flag, and YTD Flag?
Hi Silvioamartinezjr.
Glad that this video has helped you in building your dashboard.
⚡Power On!⚡ / Avi
★ Power BI Complete Tutorial: ua-cam.com/video/AGrl-H87pRU/v-deo.html ★
★ Subscribe and Join me LIVE for Talk Power BI Fridays ua-cam.com/users/PowerBIProlive ★
Avi. All of the calendar table downloads that I have done do not include the most recent YTD, MTD. QTD flags that you referred to in your video. I would appreciate if you could indicate the exact file link to ensure that I am obtaining the correct most current version. Thank you.
V nice indeed! Thanks a bundle!
Thank you! Cheers!
Hi Avi Singh, hope you are doing well.
I am facing an issue in creating a dax calculation for YTD using Between Dates. When using YTD with Between dates the values should only change when we select the Max date but in my scenario the values are changing while selecting the min date.
Please can you help me out with this. Thanks.
Hi couple you please help me to understand IT spend analysis available in Power Bi samples
Thanks AVI, also can we do this same with DAX
Hi Avi,
Thank you for the explanation.
Just wondering how we can implement this concept in Financial year calendar where the month start from July and end by June.
Hey Niraj, sorry for the late response. You can adjust the formula in Query Editor to use Juny 1 as the start of the Year.
If you're still stuck, pre-submit your question and join me in my next LIVE TalkPowerBI Q&A show and maybe I can help you there.
👉 www.learnpowerbi.com/talkpowerbi#qa
@@PowerBIPro Would love to know how to calculate a fiscal month offset and not just the month offset if you'd be kind enough to share.
how do I set the parameter to override the current date in the table? i'm working with reports with information from two months prior and need to make YTDs to that month
YTD flag follows the currentdate. Is it possible to make the YTD flag dynamic so it follows the offset I use in the report? Even with a current month offset for all pages, the YTD_flag show per TODAY()
Love your videos Avi! At the 3 minute mark of the video you are referencing a year over year trend line chart that starts over each year and then it flat lines as you don't have any data for those months. You mentioned that there is a way to solve this flat line (hopefully by not showing those data points as they are in the future). Can you please put out a video which covers this topic of modifying a line chart to remove the flat line. I have several month over month and year over year line charts that either fall to zero as they don't have any data for those months or if it is comparing yearly totals it repeats the last value for the rest of the year. PLEASE HELP!!!!
Hi Avi, what if the current MAX month is 8 but you wanted to see the YTD values for months 1-4 using a filter to define the months you want to see data for? I'm having trouble with that because I'm looking at the max month only and am unable to figure out how to define the formula to use whatever the month number filter is showing to show me YTD through that month, not just the max
I have a date slicer which should show last 12 months as selected when report opens. Further, user should have flexibility to select any other date range. Please any one answer this question
HI Avi, love the video - I've noticed that YTD flag doesn't work for leap year. For example, if I try using YTD flag today (29th Feb), I still get March (March 1st) number for 2019. What would be the solution here? Thank you!
please, If i want to instead calculate the cumulative of this year by that of last year base on the same number of months, how can i proceed ??
Hi Avi, thanks for your reply. Just wanted to know how to create a percentage measure of calculated measure value. I have create a one measure Revenue YTD. Wanted to calculate Revenue YTD % by taking measure value. Thank you.
Hi Leena. Revenue YTD% is that % Year over Year change?
Well, the steps would be
1) Revenue YTD
2) Revenue YTD Prior Year = CALCULATE([Revenue YTD], SAMEPERIODLASTYEAR('Calendar'[Date])
3) Revenue YTD Change = [Revenue YTD] - [Revenue YTD Prior Year]
4) Revenue YTD % Change = [Revenue YTD Change] / [Revenue YTD Prior Year]
If you're still stuck, pre-submit your question and join me in my next LIVE TalkPowerBI Q&A show and maybe I can help you there.
👉 www.learnpowerbi.com/talkpowerbi#qa
⚡Power On!⚡ / Avi
★ Power BI Complete Tutorial: ua-cam.com/video/AGrl-H87pRU/v-deo.html ★
★ Get Your Questions Answered LIVE on TalkPowerBI Fridays www.learnpowerbi.com/talkpowerbi ★
great video
Hi Avi, the Flag_YTD column you added only captures data back to 1 Jan. Is there a way to include data back to 1 July of the previous year to create a Flag_FYTD column? Many thanks.
Hi Scott Davies.
Yes that should absolutely be possible. Try changing the logic in the Query Editor. If you're still stuck, join me in the TalkPowerBI show and ask me there. I go live each Friday. Link below.
⚡Power On!⚡ / Avi
★ Power BI Complete Tutorial: ua-cam.com/video/AGrl-H87pRU/v-deo.html ★
★ Subscribe and Join me LIVE for Talk Power BI Fridays ua-cam.com/users/PowerBIProlive ★
Thank you very much for your video. Can I have a question? How can I add the mentioned CurrDayOffset into the calendar? I have tried but no success. Thank you in advance.
Hi Monika Havlíková. Have you checked out my Ultimate Calendar Series and downloaded the Ultimate Calendar?
See if CurrentDayOffset is already a part of that.
👉 Ultimate Calendar Series ua-cam.com/video/BtYn1hfdSAM/v-deo.html
👉 Download Ultimate Calendar web.learnpowerbi.com/download/
And if you have any additional questions you can get them answered in our LIVE TalkPowerBI Q&A show.
👉 www.learnpowerbi.com/talkpowerbi#qa
⚡Power On!⚡ / Avi
★ Power BI Complete Tutorial: ua-cam.com/video/AGrl-H87pRU/v-deo.html ★
★ Get Your Questions Answered LIVE on TalkPowerBI Fridays www.learnpowerbi.com/talkpowerbi ★
Nice explanation and great concepts!
Glad you liked it!
I have a column in my table name reason for leaving and based on that I want to add another column which is Volunteer attrition or Involunteer attrition i.e. if reason for leaving for an employee appears as Retired then the new column should show Involunteer attrition whereas for resignation it should show as volunteer attrition. Can you help me add this column please? Also how can I calculate the percentage of volunteer and involunteer attrition based on the total attrition for the whole year which is 1157.
Avi, always love your content. Thank you.
I am stumped (frequently) by what I'm about to ask. I tried to follow your example here. I have a model that uses your ultimate calendar table so we should be apples-to-apples. When I go to type in your DAX - MaxMonth = CALCULATE(max('Calendar'[MonthNum], ALL('Calendar'), ____________________) I can not get the editor to give me 'Calendar' [CurMonthOffset] as an option to add to that formula. What causes this?
This is a good question. Kindly send it to- avi@avising.com and we will try and respond from there . Also include as much detail as possible. Here is an article on how to get your Power Bi questions Answered. ----> www.linkedin.com/pulse/how-ask-good-power-bi-questions-get-them-answered-singh-powerbipro-/ ^RM
Hi Sir, is it possible to give year end date dynamically in TotalYTD DAX , so can u pls help me with this if there is a way.
Dear Avi, I like your 'ultimate calendar table' and thanks for this video. The idea of using YTD as flag in that table is great. However you are using the M function 'dayofyear'. Don't you get a problem whenever you compare normal years with leap-years?
Does anyone know why some rows are showing as "null" in the Ultimate calendar Flag_YTD, Flag_MTD and Flag_QTD columns? Thank you!
How to make last year YTD based on using current month offset? Please advise
How could i generate a dynamic table of Current/YTD/MAT with multiple selections of year month slicer?
Thanks Avi. I decided to use the QE/flag approach and it works great for calendar days. Question: how do I get DayofFiscalYear where the first day is say July 1st and day 365 is June 30.
Hmmm...DayOfFiscalYear, that's an interesting one. Here's what I can think of off the top of my head (can talk more on...TalkPowerBI 🙂).
1. Create a Subtable with only the First days of Each FiscalYear (FirstDate, FiscalYear)
2. Merge Calendar Table with SubTable on FiscalYear
3. Expand the "FirstDate" Column
4. Add a Calculated Column, [Date] - [FirstDate] + 1
⚡Power On!⚡ / Avi
★ Power BI Beginner Tutorial: ua-cam.com/play/PL7GQQXV5Z8ef2SjkDpLnvsz7TAQjlzlpO.html ★
★ Subscribe and Join me LIVE for Talk Power BI Fridays ua-cam.com/users/PowerBIProlive ★
@@PowerBIPro sweet. will give that a try and will see you tmr on TPBI
Thanks you for your help, I have a doubt, how could I get the year to date growth from this example
Hi Eduardo Vazquez.
YTD growth would be a different approach using a DAX measure.
You would use the DATESYTD function. Try that if stuck join me in the next TalkPowerBI (I go live each Friday) and ask me there. Link below.
⚡Power On!⚡ / Avi
★ Power BI Complete Tutorial: ua-cam.com/video/AGrl-H87pRU/v-deo.html ★
★ Subscribe and Join me LIVE for Talk Power BI Fridays ua-cam.com/users/PowerBIProlive ★
How to solve that flat line in cumulative calculation?
Hi Avi,
My requirement is also to compare this year performance to previous year ...but the data in my case is also partial but not in series ...like random months data is missing ..Can you please help me figure out dax logic pls
Hi Avi, I have data for 'Actuals' as of May 2020 but the CurMonthOffset shows 0 against August 2020. I want it to show May 2020 as 0. Note that my dataset has projected values too & hence I have data upto Dec 2020. Can you please suggest Avi?
Hi Arun Shenoy.
You would need to launch the Query Editor ("Transform Data"), go to the Calendar Query and update the Logic that Calculates CurMonthOffset to calculate it NOT based on Current Month (August) but based on the Last Month Data is available.
If you have any additional questions you can get them answered in our LIVE TalkPowerBI Q&A show.
👉 www.learnpowerbi.com/talk#qa
Or Join our LearnPowerBI Private Community
👉 web.learnpowerbi.com/waitlist-invite/
⚡Power On!⚡ / Avi
★ Power BI Complete Tutorial: ua-cam.com/video/AGrl-H87pRU/v-deo.html ★
★ Get Your Questions Answered LIVE on TalkPowerBI Fridays www.learnpowerbi.com/talk ★
@@PowerBIPro can you help with the formula to update for Arun's question. I have same scenario my actuals are 1 month lag.
Please do a video on dynamic changing dimension using DAX
Could you dive more into M? Best functions helpful tips and tricks...?
Hmmm..best functions. Never thought like that. I usually just work with real data and do not focus on what function/steps I am using. Instead
understanding patterns and just knowing how to solve for those (e.g. variable number of header rows, repeating new header, unflatten data etc).
My paid course does dive deeper into Query Editor.
In fact it has modules to dive into each core area - Query Editor, Relationships, DAX, Visualization, PowerBI.com Platform.
Course Waitlist is here web.learnpowerbi.com/waitlist-invite/
⚡Power On!⚡ / Avi
Hi Avi
Is there any formula for Half To date ? For one semester cumulative dynamic total
Thanks
Hi how do if financial year start on April and end on March that time how to compare YTD and MTD please advise me how do it in power BI
Kulkarni, this one is easy.
👉 Use my Ultimate Calendar Table. Watch Video here: ua-cam.com/video/zXZAZrUwUe8/v-deo.html
👉 Download files from here web.learnpowerbi.com/download/
Then in the Ultimate Calendar change the FiscalYearMonthEnd to March (or may be it's FiscalYearMonthStart to April...check the file).
Thn in functions like DATESYTD, specify the optional second parameter to say that your year ends in March.
e.g. CALCULATE([Sales], DATESYTD('Calendar'[Date], "03/30").
That's it! If you're stuck join me for one of my LIVE TalkPowerBI shows (link below).
⚡Power On!⚡ / Avi
★ Power BI Beginner Tutorial: ua-cam.com/play/PL7GQQXV5Z8ef2SjkDpLnvsz7TAQjlzlpO.html ★
★ Subscribe and Join me LIVE for Talk Power BI Fridays ua-cam.com/users/PowerBIProlive ★
Thank you for sharing so an interesting video, although it's not answering for scenario where we"re talking about dates instead of months.
Hey Mike,
Great point! Were you trying to get it by day? Or have you already solved it?
If you are still figuring it out, let me know, I'll try to help if I can in my next video :-)
Power On! / Avi
Hi Avi, thank you for your response.
I'm actually talking about a scenario where on the one side I have a fact table that is related to the calendar table by date key and on the other side I have a target table that has a month number column instead of a date column and should be related with a calendar table.
experiencing this error: A function 'PLACEHOLDER' has been used in a True/False expression that is used as a table filter expression. This is not allowed.
any fixes?
HI Great video. I have one doubt. I have downloaded last version of ultimate calendar and the current date is automatically now. How can we override an put another date?
How could be created Year -over-Year in the same way?
how to show sales difference in percentage through column chart. Please help. I am unable to find percentage of Measure value across years
Hi Leena. Are you looking for simply Sales Year over Year % Change? I am pretty sure that is covered in my main tutorial.
Check it out at ua-cam.com/video/AGrl-H87pRU/v-deo.html
And if you have any additional questions you can submit them here for our LIVE TalkPowerBI Monthly Q&A
👉 www.learnpowerbi.com/talkpowerbi#qa
⚡Power On!⚡ / Avi
★ Power BI Complete Tutorial: ua-cam.com/video/AGrl-H87pRU/v-deo.html ★
★ Get Your Questions Answered LIVE on TalkPowerBI Fridays www.learnpowerbi.com/talkpowerbi ★
I'm trying to do something similar except with Fiscal Year To Date. Instead of the year starting any January, it needs to start in October. If you have any suggestions let me know.
Hi Drew Kozicki,
Sorry for responding so late. If you're still stuck, pre-submit your question and join me in my next LIVE TalkPowerBI Q&A show and maybe I can help you there.
👉 www.learnpowerbi.com/talk#qa
⚡Power On!⚡ / Avi
★ Get Your Questions Answered LIVE on TalkPowerBI Fridays www.learnpowerbi.com/talkpowerbi ★
Will you please share file link
one issue with this solution is what if the month is incomplete? Then comparing with prior years are still not apple to apple. In this case prior years full month, and current month mid if month?
Hey Gourmet,
Option A: You can either only consider Completed months ([MaxMonth] - 1) or...
Option B: You can compare data only till the Current Date. I have a related video on this at ua-cam.com/video/WBddNp_25YY/v-deo.html
Power On! / Avi
Do check out our LIVE events at www.TalkPowerBI.com
Hi Avi. I'm not able to see the YTD Flag column in the ultimate calendar table.
Marianne from Team Avi. Sorry Avi, was not able to get to your comment. If you're still stuck and need help we would recommend submitting the question
to our LIVE TalkPowerBI Q&A Session www.learnpowerbi.com/talkpowerbi#qa
@@PowerBIPro I second Mohamed and the many other viewers who are commenting that the Ultimate Calendar that is being made available to download DOES NOT contain any of the flags Avi mentions in this video. So, apparently Avi updated his table, but not the link to download it. We are stuck with an older version of the pbix file. Is that clearer, Marianne? I hope I have helped, but English is not my native language.
@@conradomecchi1841 Got it! Let me check what's going on.
Am I wrong that this is still not apples to apples if the current month is incomplete? Do you have an example of to the actual DATE not the end of month? tia
Hi Avi, please do share the excel data to practice the same
may I ask how can you rank the month in correct order ?
Hey, that uses the "Sort By Column" Functionality. That should be covered in one of these videos I think: ua-cam.com/video/X2aMi_Ekfyw/v-deo.htmlsi=jb0zQn7wMCQXNJ1Q
ua-cam.com/video/CJ2y6v4mQZk/v-deo.htmlsi=RltLpE7ptMn7UM2U
Power On!
It was giving error without using variable.
ofcourse second one is much easier.