DAX Fridays! #18: YTD,QTD, MTD 

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

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

Yey!

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

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

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

Thanks, I was wondering the same!

Thanks for the feedback! /Ruth

+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

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

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

🥳🥳 /Ruth

I wont 😂

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

+Shubham Sharma Hi Shubham, Here you have it : m.ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE--tg0m-WT1xE.html /Ruth

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

@@CurbalEN Thanks a lot :)

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

@@CurbalEN Thank you for quick reply

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

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

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

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

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

Thank you. I did it with DATESBETWEEN :-)

Perfect :) /Euth

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

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

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

+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

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

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

Curbal Thank you curbal

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

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

+Karim Karang DIEME Hi, check this video, I think it will help you out! ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-PlSKlFGg-JQ.html /Ruth

+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

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

Great. Thx

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'

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

+José Bernáldez Hola José, muchas gracias por tu comentario! Puedes utilizar la función DATEADD para eso. Has visto el vídeo? m.ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-iw-WfZM9L3U.html /Ruth

+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

Hi Ashik, Can you please contact the Power bi Community ? Here is a how-to guide for best results: m.ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-oXFOebuvRz0.html /Ruth

@@CurbalEN thanks Ruth

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

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

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

Curbal excel 13

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

You need a calendar! /Ruth

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

+Rodrigo Bedoya Zuluaga Hola Rodrigo has visto este vídeo? m.ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-ju_KPc3Fvg8.html /Ruth

Hola Alberto , TODAY() /Ruth

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

De nada :) /Ruth

@@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.

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

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

@@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.

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.

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

@@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.

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

Thanks a lot

You welcome! /Ruth