DAX Fridays! #18: YTD,QTD, MTD

[@just4fun410]

Hello, I have gone through many videos, but all the videos are very critical , but the way you are explaining is really good and the examples are really very useful. Enjoyed a lot & Thanks a lot Curbal.

[@CurbalEN]

+vamsi goud and thanks to you for taking the time to give me feedback, really appreciate it! Have a great weekend 🏝 /Ruth

[@Victor-ol1lo]

Thanks for the great video Ruth !! Looking forward to see more DAX tricks next Friday !!!

[@CurbalEN]

+Victor Friesen Thanks Victor! Glad you liked it :D /Ruth

[@SlippingDownMan]

Hi, I'm wondering why in your TOTALYTD examples you've used DATESYTD(DimDate[Date]) instead of just DimDate[Date]. OK it works, but it seems redundant/unnecessary to me to use DATESYTD when TOTALYTD should be doing the calculations using dates ytd anyway. Similarly why use DATESMTD in the TOTALMTD formula when just the date should do?? This raises the idea of using the WRONG DATES function such as TOTALYTD([Total Sales],DATESMTD(DimDate[Date])) which is or course a silly thing to do but if we are using a DATESYTD or DATESMTD inside TOTALYTD or TOTALMTD then this becomes a possible error, whereas if we are using just the date, it's not only simpler and still correct, it's less error-prone?... thanks

[@CurbalEN]

You are correct, once you use TOTALYTD, you don’t need DATESYTD as it is “built in” the function. /Ruth

[@YYLfrank]

appreciate the proof of my concern, this comments i like to push up to top.

[@scramiro1]

Thanks, I was wondering the same!

[@CloudhoundCoUk]

Excellent presentation. Key learning point, for me. Check, your data results are what you expect. It's all too easy to just assume a function generates the correct results.

[@CurbalEN]

+Cloud Hound Hi Christopher, I have to admit that I still make that mistake myself... too eager to know the results... but practice makes perfection right? /Ruth

[@CloudhoundCoUk]

Embarrassing, like you I'm to eager despite knowing assumptions are likely to result in output errors. Just have to force myself to check.

[@CurbalEN]

+Cloud Hound Me too! :) ... Funny! /Ruth

[@phanidivi3613]

mam,what an explanation.Its really very nice.After kud venkat ,your are my best trainer.

[@CurbalEN]

Thanks for the feedback! /Ruth

[@jegadeesr7213]

Hi Curbal, Could you please explain how did you setup today's date to old date?

[@NorbertoVeraReatigaNVR]

Excelente vídeo, iniciare mi practica de estas funciones de tiempo, muy útiles! Gracias Ruth...

[@CurbalEN]

+Norberto Vera Reatiga Gracias a ti Norberto ;) -/Ruth

[@bradj229]

Thanks Ruth! Great video!

[@CurbalEN]

Yey!

[@KavyaTalks-Malayalam]

Hello Ruth Can you please show how to subtract 2 time periods in PBI? For ex.14:12:10 - 16:13:20

[@aonline-training]

Hi Ruth, First, Thank you again for your tutorial's they really have been great. Please do answer my questions. I'm unable to get the difference between TotalYTD & DATESYTD tOTALytd = TOTALYTD(SUM(FactResellerSales[SalesAmount]),DimDate[FullDateAlternateKey]) 2DatesYTD = CALCULATE(sum(FactResellerSales[SalesAmount]),DATESYTD(DimDate[FullDateAlternateKey])) New_Total_YTD = TOTALYTD(SUM(FactResellerSales[SalesAmount]),DATESYTD(DimDate[FullDateAlternateKey])) My Question: 1) How did you set today;s date as 1998, i wrote a measure, as max date, but that does not reflect automatically as today's date ​2)​ The Measure, tOTALytd & 2DatesYTD Give me identical answers. 3) For New_Total_YTD Measure, Why would I use, DATESYTD, when I have a table ( my unique dates are in Dates Table ) And, when I have already written TotalYTD, then wouldn't this get the job done, i did that. Took the same measure, and only replaced datesYTD with date, keeping, the function TotalYTD, i again got the same answer. Please do answer me, Always appreciated your quick reply :) Thank you again for such a wonderful playlist, and making me fall in love with "DAX" Regards & Respect

[@CurbalEN]

Hi! Do you mind to post your question in the Power bi Community? My time is sooo Limited! Thanks! /Ruth

[@mohshalsh5965]

How could i generate a dynamic table of Current/YTD/MAT with multiple selections of year month slicer?

[@gudavenkatakrishnareddy]

Hi Curbal, Good morning How can I create PTD(project to date) Means select month to all previous month's )?

[@אלכסנחשונוב-פ5ע]

Hi small Question, I created three measure YTD, QTD and MTD for some reason the first two work but the last one MTD is not returning any values, Any idea why that is? Thanks,

[@gayathreenatarajan2991]

Hi, Thank you for all the videos you are posting, really helpful. I have doubt in setting up default month. My powerBI report should count the number of records for each month and its average, I managed to do that but I want to give a default value where it filters current month when the report is opened by users later the user must be able to change according to his choice. Can you suggest? I cant find any matching tutorials or example for my scenario.

[@CurbalEN]

There are a lot of ways to do that, none are straightforward. Here is one example: community.powerbi.com/t5/Desktop/Current-month-default-selection-in-Slicers-so-that-it-will-show/td-p/385492 Ask the Power Bi community if that is not what you were looking for! /Ruth

[@gayathreenatarajan2991]

@@CurbalEN Thanks a lot :)

[@frankgovers8420]

Hi Ruth, in THE TOTAL YTD, why use DATESYTD? YTD Sales = TOTALYTD([Total Sales];DATESYTD(DimDate[Date])) this gives the same result YTD Sale2s = TOTALYTD([Total Sales];DimDate[Date])

[@CurbalEN]

Hi Frank, TotalYTD is syntax sugar for CALCULATE([tot sales],DATESTYD(date) so ytdsales2 is more correct. ;) /Ruth

[@MTBlagunaMtycillo]

Ruth, aplique la función TOTALMTD y me acumula la información del mes actual y el del año anterior, sabes si tengo que usar algún tipo de filtro?

[@CurbalEN]

Mmmmm no, no necesitas ningún filtro, algo estás haciendo mal. Puedes poner la pregunta en la comunidad de Power Bi? Gracias ! /Ruth

[@DanielWeikert]

Hey Ruth, i noticed that you could also use the TOTALMTD function without DATESMTD. Gives me the same result. Do you know any differences between using and skipping it? Thanks and keep going

[@CurbalEN]

Hi Arcain, Great question! TOTALMTD is just a DATESMTD in disguise: TOTALMTD= CALCULATE( [measure] ,DATESMTD(DimDate[Date])) /Ruth

[@DanielWeikert]

Great. Thx

[@tonyz1616]

Hi Ruth, actually I have the same confusion with the video, and then I run into this comment. If I may add, TOTALMTD function did often use without DATESMTD (if I searching through other resources including Microsoft document). So I think the confusion is should we use : 1. MyMeasure = TOTALMTD([TotalSales] . DimDate[Date]) or 2. MyMeasure = TOTALMTD([TotalSales], DATEMTD(DimDate[Date]) I love all your tutorial though. Keep rockin'

[@CurbalEN]

The results will be the same, but the idea is to do either; TOTALMTD with dimdate or CALCULATE with DATEMTD, but any combination will work. /Ruth

[@abrahamlatapie]

Thank you so much, your videos are extremely useful. I have a question, i have a case that the 1st day of operations are the 26 of the month and the last day its 25, it is similar to the fiscal years but for months, what can I do? please heeelp me!!

[@udaytarak6699]

Hi, hope you are doing well. I am facing an issue with YTD dax calculation using Between dates in the power bi report. Please can you respond to my question on how to make YTD calculation working by selecting MIN & MAX dates. Please respond.

[@venkateshthammisetty7845]

Hi Ruth, is it possible to give year end date dynamically in TotalYTD DAX instaed of fixed(4/30) , so can u pls help me with this if there is a way.

[@ajeet503]

Could you please help me, Where i can get this google Analytics video becuase provided link not working

[@hariannam7408]

Hi could you please tell me how to create full year (FY) measure.

[@CurbalEN]

+Hari Annam Hi Hari, could you please post your question in the power bi community? They will help you in no time ;) /Ruth

[@hariannam7408]

Curbal Thank you curbal

[@MTBlagunaMtycillo]

Ruth, how to calculate Todays Date?

[@CurbalEN]

Hola Alberto , TODAY() /Ruth

[@MTBlagunaMtycillo]

@@CurbalEN Ruth, pense que por los datos de ejemplo habían hecho algo diferente. Muchas gracias.

[@CurbalEN]

De nada :) /Ruth

[@StephenBoothUK]

@@CurbalEN I presume that's not the function used here, as 'Today' for this data set is about 20 years ago compared with TODAY(). I figure it would have to be the date of the last order in Orders (max(Orders[OrderDate])) but that would be impacted by the year slicer so i thought the fix for that would be (my Dates table is the equivalent of your dimDate): Latest Order Date = calculate(max('Orders'[OrderDate]), all(Dates[Date])) Which I understand to mean "For all of the dates in the Dates table, ignoring any filters on Date, find the largest OrderDate in the Orders table" but that doesn't seem to work when the slicer is based on the Year coloum of the Dates table, when I switch the slicer to use Dates[Date] it works or if I keep the slicer on Year but change the filter in Latest Order Date to use Dates[Year] then it works. Guess this is a gotcha on ALL() (and presumably ALLSELECTED()), it only removes filters on the specific column named, not another column in the same table that maps to that column (each year has multiple dates but each date has one and only one year). That could be quite frustrating and mean you have to either base slicers on a different field than you wanted or have multiple versions of the same measure if you might need to slice by different columns of the date table (e.g. one page you want to slice by year such as year but in another you might want to slice by Month or Day of Week, and display the date of the first and last orders placed not the first order placed on a Tuesday or in a September). I wondered if maybe a single measure using FILTER() and multiple ALL() functions ORed together would work: Latest Order Date All = calculate(max('Orders'[OrderDate]), filter(Dates, all(Dates[Year]) || all(Dates[Date]) || all(Dates[DayOfWeekShortName]) || all(Dates[MonthName]))) But that creates fine but then errors if put into a visual as it returns a table.

[@CurbalEN]

Hi Stephen, today() returns today’s date , that is the date on your computer. It is the same as NOW() but without current time. /Ruth

[@EdwinGrootoonk]

Question. I wan to calculate the percentage of increase of decrease in sales compared to last year. I came up with: (page year filter is 2017) Sales PYTD = calculate (sum(table[sales]); sameperiodelastyear (dimdate[date])) Sales YTD = sum(table[sales] % Sales PY = Sales YTD / Sales PYTD * 100 My problem is that it does Sales YTD correctly from 1-1-2017 until the current date, but the PYTD is calculated for the entire year (this because of the page filter 2017 I assume). I expected the date ranges of PYTD to be also from 1-1-2016 until the current date minus one year (if it was today it would be 17-5-2016) What am I missing

[@CurbalEN]

Hi Edwin, Have you seen this thread? community.powerbi.com/t5/Desktop/YTD-last-year-DAX/td-p/108482 /Ruth

[@shubhamsharma194]

Where can I find the video for creating the DimDate Table ? you mentioned in your video that you have a video on that. Please help.

[@CurbalEN]

+Shubham Sharma Hi Shubham, Here you have it : m.kzbin.info/www/bejne/Y6XKYaBjjLlkrqc /Ruth

[@alihamzasameer8996]

How can we calculate mtd, ytd and qtd with fiscal years ending in June

[@tanababa]

Hi Ruth, Is there a DAX function that does the opposite from YTD? Meaning the sum from today till the end of the year? Thank you, Tamir

[@CurbalEN]

Hi! There isn’t a ready made function, but you can build it yourself with for example DATEADD or DATESBETWEEN depending on what your exact requirements are. /Ruth

[@tanababa]

Thank you. I did it with DATESBETWEEN :-)

[@CurbalEN]

Perfect :) /Euth

[@larryjones4573]

Hello Ruth. I have watched this video several times, thinking I missed something on a previous viewing, but I cannot figure out how to make the DATESMTD work in conjunction with SAMEPERIODLASTYEAR. I have built a calendar table using the CALENDAR function and my DATESMTD, DATESQTD, & DATESYTD functions all work fine for the current year. I also have PREVIOUSMONTH and PREVIOUSQUARTER measures working correctly so I don't suspect any problems with the calendar table. The trouble is in trying to calculate the same period last year. If I do a SAMEPERIODLASTYEAR(DATESMTD('Calendar'[DATE])), it gives me the full amount for the entire month last year not just the first three days of the month, assuming we are on the 3rd day of the month. Can you clarify the correct DAX syntax for just aligning the DATESMTD for last year to just calculate through the current day in the month and not report the full month's total? Many thanks for your videos. They are great! All best, Larry

[@karimkarangdieme2616]

Hello friends. How to fill data from an excel workbook in power bi? Thank you.

[@CurbalEN]

+Karim Karang DIEME Hi, Get data and then from Excel, /Ruth

[@karimkarangdieme2616]

Precisely for this there is no problem. Once my Excel workbook loads into Power BI, how to directly fill data into Power BI. Thank you

[@CurbalEN]

+Karim Karang DIEME Hi, check this video, I think it will help you out! kzbin.info/www/bejne/hp22fJ98fMxggLM /Ruth

[@cristinarueda5598]

What about this calculation but with a different beginning of Fiscal Year?

[@CurbalEN]

Hi Cristina, Just add the fiscal year to the calendar table or create a fiscal calendar. For more detailed info, check this post:community.powerbi.com/t5/Desktop/Fiscal-year-not-the-same-as-Calendar-year/td-p/15026 /Ruth

[@atudelvis2353]

you are a life saver ....... kudosssss

[@CurbalEN]

🥳🥳 /Ruth

[@manomitabaral7291]

Thanks for the wonderful explanation in this video. I have got one query , Is this possible? Ex. If I am selecting Year -2020 and Month Jan -2020 , Is it Possible to show Current Month Sales(i.e Jan -2020) and Previous month Sales(i.e Dec 2019) simultaneously ?

[@hargobindsingh8448]

Hi there, Does that matter if your Total Sales column is a measure or not? I am doing something similar for MTD but It is showing me total cost instead of MTD.

[@alekhya9834]

which function you have used to display todays date

[@harazinpavel]

Could you please reupload the PBIX sample file? The link above returns "Page not found." Thank you.

[@CurbalEN]

+Pavel Harazín Hi Pavel! I updated the link. Please try again ;) /Ruth

[@rolandkc]

Must your calendar be always built up to today's date? If you build a large calendar to include days up to the end of the decade [2020], can you specify the date to which you want your report to be based on? E.g although today is Feb 12, 2017, can I have a report based on a report date of say Jan 31, 2017 [viz. I have doing the month-end close] - i.e. my YTD, QTD & MTD must be based in relation to the report date, which is Jan 31, 2017 and not based on today's date, which is now Feb 12, 2017.

[@CurbalEN]

+Roland Kc Hi Roland, Here is a summary of all the calendars I have covered so far: curbal.com/blog/create-power-bi-custom-calendars /Ruth

[@josebernaldez]

Hola, no sabes como me ayudan tus videos en el día a día.... tengo una duda, como puedo comparar YTD/MTD vs YTD/MTD del mismo periodo del año anterior? estoy usando esta formula: . YTD PY = TOTALYTD([. Total Sales USD],PREVIOUSYEAR(DATESYTD('Calendar'[Date])))

[@CurbalEN]

+José Bernáldez Hola José, muchas gracias por tu comentario! Puedes utilizar la función DATEADD para eso. Has visto el vídeo? m.kzbin.info/www/bejne/n6iQiJmQgp5_abc /Ruth

[@grimes-williamshome2072]

Is your dates table joined to the other tables?

[@CurbalEN]

No, I don’t think so. You can download the sample file from the download center: curbal.com/donwload-center /Ruth

[@grimes-williamshome2072]

@@CurbalEN Thank you for such a quick reply, especially on an older video. I did find that file after posting this, and it is joined to at least one of the tables.

[@grimes-williamshome2072]

I just realized I needed a date table for my MTD, QTD, YTD functions when all of my calculations broke when January hit. So I have been going through quite a few of your videos regarding this. Thank you for all of your help.

[@CurbalEN]

With join you mean with relationships? I thought you meant merged! Sorry about that! Hope the videos help ;) /Ruth

[@grimes-williamshome2072]

@@CurbalEN Oh, yes. I meant relationships. So you do have to have a relationship to the tables that will use the date function, correct? For example, if I am trying to calculated YTD Sales, I would need relationship between my Sales Table and the Date Table.

[@anjim8507]

What is the use of DATESYTD,DATESQTD,DATESMTD and why we used it. What is the difference between TOTALYTD and DATESYTD

[@atudelvis2353]

Hi Curbal, i tried using your formula to get the YTD value for same period last year ( SPLY = CALCULATE([m_sum_totalsales],DATESBETWEEN(Dates[Date],FIRSTDATE(DATEADD(Dates[Date],-12,MONTH))MAX(Dates[DatePY]))) keep getting the wrong answer, also any idea how to write the DAX for same quarter last year? and idea will be hugely appreciated

[@rodribezul]

Hola interesante tu canal. Una inquietud. Así como hay formato condicional para colocar colores dependiente de una condición de valor, hay algún forma de generar un icono o imagen tipo semáforo para el resultado de un porcentaje. Por ejemplo mido el presupuesto/ejecutado pero que este resultado lo vea grafico. Algo parecido a los formatos condicionales en excel. Gracias

[@CurbalEN]

+Rodrigo Bedoya Zuluaga Hola Rodrigo has visto este vídeo? m.kzbin.info/www/bejne/oKbCfIOZaKupnZo /Ruth

[@kistipurwitosari7781]

If I want to calculate YTD 1997, it's not Full Year in 1997 but until May 1997 like YTD 1998 until May 1998. So do I if I want to calculate YTD 1996, it's until May 1996. Thanks for the solution

[@CurbalEN]

Can you ask in the power bi community? Thanks! /Ruth

[@powershah]

Hi Ruth, Thanks for the another great video and supporting the community with your expertise. Kindly clarify me on, what's the difference between Total YTD formula which you used in this video and using the formula Calculate with DatesYTD formula. Have you made any video on calculating monthly growth over last years same month. Thanks very much.

[@CurbalEN]

TotalYTd is syntax sugar for calculate and DATESYTD :) Maybe on the samepoeriodlastyear? /Ruth

[@AshikKhan]

Hi Ruth, I am facing difficulties in defining YTD, LYTD I don't get proper results. Even after watching previous video on calendar, I have tried to replicate the same but hard luck... I couldn't make it. Can you please help me

[@CurbalEN]

Hi Ashik, Can you please contact the Power bi Community ? Here is a how-to guide for best results: m.kzbin.info/www/bejne/pYmpgJiYqtuFsJI /Ruth

[@AshikKhan]

@@CurbalEN thanks Ruth

[@andresss85]

How can I get the total for a month and the total for the last month? I would like to have a table with : Month, TotalSales, TotalSalesLastMonth Could you please help me with this?

[@CurbalEN]

+Andrés Castrillo Hi Andrés, Please post your question in the power bi community. It is likely that if you search there, you will find an answer without posting. /Ruth

[@SyedUsman]

All of these Dax Expressions returns blank in Measure??? any idea what i am doing wrong

[@CurbalEN]

+Syed Ahmed Hi Syed, have you created a calendar table? /Ruth

[@SyedUsman]

Curbal yes i have . for all ytd qtd mtd . i get (blank)

[@CurbalEN]

+Syed Ahmed Are you using excel or power bi? /Ruth

[@SyedUsman]

Curbal excel 13

[@CurbalEN]

+Syed Ahmed ok, we are getting closer. Have you put your measure in a pivot table with dates on it? /Ruth

[@AshikKhan]

Hi Ruth, what if there is no date column, I have only month and year, then how do I do this calculation

[@CurbalEN]

You need a calendar to use Time Intelligence functions. /Ruth

[@AHMEDALDAFAAE1]

Thank you fot the video, I have a question what is the differencr between 'YTD_DatesYTD_Orders:=TOTALYTD([Sum ordered],DATESYTD('Calendar'[Date]))' And YTD_DatesYTD_Orders:=TOTALYTD([Sum ordered],'Calendar'[Date]), in which DATESYTD exists on one and not on the other? For me, they return the same results no difference

[@CurbalEN]

They do, TOTALYTD contains DATESYTD (syntax sugar) but you cant see it. Hope this helps! /Ruth

[@AHMEDALDAFAAE1]

@@CurbalEN Thank you for quick reply

[@abhishekstatus_7]

Hi Ruth, Great video !! Could you please help me if i have only month columns and the related data for those months by column wise and I want to calculate YTD for each without having calendar table or any other filed related with date ,month or year..If you want i can share the dataset with you !!

[@CurbalEN]

You need a calendar! /Ruth

[@abhishekstatus_7]

@@CurbalEN Thanks but when I created the calendar table and relationship between the calendar and month table its not working for YTD calculation

[@juanignacioXI]

Now watching this to apply in my report, thanks again Ruth!! Dont think im stalking you haha..

[@CurbalEN]

I wont 😂

[@paolomastrantoni3367]

Hi, thank you very much for your videos, They are my first guide on powerBi word. My data table is from 2000 to 2030. I notice that YTD works correctly only if I select by a slicer a year I make some tests with DAX Studio and I think it happenend because TOTALYTD/DATEYTD consider by default the last year in the DateTable. In my case they try to sum Sale of the 2030. . There is a way to put in the formula the ref to the current date (TODAY) so that YTD/MTD/QTD set automatically correct without slicer in the page? I tried this way (for MTD, the problem is the same) but... it doesn't work: MTD = TOTALMTD([TotSale]; DimTime[Date]; filter (LASTDATE (DimTime[Date]) ;DimTime[Date]

[@anithamg7114]

thanks for your video. I tried a alot to understand your explanation, but I can't. Please explain well.

[@CurbalEN]

+anitha mg Hi Anitha, Sorry you couldn't understand my explanations. Try to search the web to see if you can understand other's explanation better. /Ruth

[@girishrainbow]

How to find week to date???

[@CurbalEN]

Follow this tutorial: curbal.com/blog/the-ultimate-dashboard-to-track-your-website-growth-with-google-analytics-and-power-bi-supercharged-excel /Ruth

[@girishrainbow]

Thanks a lot

[@CurbalEN]

You welcome! /Ruth

[@harikabhavirisetty4094]

Can you please explain process for the getting the below result Data Data Input Required Output 2021_11 2022_01 2022_03 2021_11 2021_12 2022_01 2022_02 2022_03 ABC 44 251 1286 44 44 295 295 1581