For those, like me, that tried to make it work with WTD as well, i made it like this: var WeekStart = CALCULATE(TodayDate-WEEKDAY(TodayDate,2),YEAR(Data[Data])=YEAR(TodayDate))
Hey .... Nice! can you help in getting the ptd done in same way .... ex if the start of fiscal preiod is as below fiscal_period_num fiscal_period_start_date fiscal_period_end_date 1 2023-01-29 2023-02-25 2 2023-02-26 2023-03-25 3 2023-03-26 2023-04-29 4 2023-04-30 2023-05-27 5 2023-05-28 2023-06-24 6 2023-06-25 2023-07-29 7 2023-07-30 2023-08-26 8 2023-08-27 2023-09-23 9 2023-09-24 2023-10-28 10 2023-10-29 2023-11-25 11 2023-11-26 2023-12-30 12 2022-12-25 2023-01-28 12 2023-12-31 2024-02-03
Hey there, a really well explained video. This was the first real DAX work I've done and I managed to follow it and create the functionality. I even used the prinicples to add a "Yesterday" in there as well!
Thanks! Yeah there's another great video follow up to this one too that talks about other time intelligence perspectives. th-cam.com/video/xCHnjzgfLG8/w-d-xo.html
Hi! thank you for this wonderful video! I have a little question, what is the information from Data[month year]? because I have a table with 12 dates (start year date 09/30/2019 and end year date 08/31/2020) but when I replace the information with my table it got an error : "The start date in Calendar function can not be later than the end date". Could you please let us know how can we replace the table month year?
if you wanted to change the order of the items in the slicer so it shows YTD, QTD, MTD you can add a column to the table in the code and then sort the selections based on that. For Example - ADDCOLUMNS( CALENDAR(YearStart, TodayDate), "Selection", "YTD", "Order", 1)
I would like to add previous Month to the selection + 3prior months PreviousMonth = if current month is march, previous month would be 01/02/2023 - 28/02/2023 3 prior months : if current month is march, prior 3 months would be 01/12/2022 - 28/02/2023
Thank you for this video! Quite Impressive. I'm working on something, based on the video MTD/QTD/YTD I created the selection and the calculations seem to work fine. I had one question can we also compare with PMTD/PQTD/PYTD as well at the same time basis the MTD/YTD/QTD selections. Thanks
Nice use of relationship! I will definitely try this out. Normally I use the pattern with disconnected slicers and switch formulas so I'm excited to see this from a performance perspective. If the selection table can be calculated in PQ it can be stored in data flows for reuse in other models, but alot more fun crunching out a good DAX code ;)
It's a bit tricky, since you can't point this DAX Generated table at your calendar table without creating a circular dependency error. Two solutions I can think of: A) if your calendar table is made in PQ, you can point this table's logic to the date column on that table. This would be my recommended option. Or option B) is create a separate disconnected DAX calendar table using the CALENDER function, same start/end date as your calendar table. Then hide it in your model, and point the date selection logic to that date field in there, since it's a continuous date range.
@HavensConsulting thanks for this optimized option. But, I am facing the circular issue despite creating the date table in PQ. If that can be mitigated, it will reduce redundant measures
Super video, very helpful. I have a question: what if I would like to select a month so to calculate YTD and MTD of that month, without using any formula, could I make it? Thank you.
Great video. Playing with this (as a newbie to PBI) I have recreated it in PQ but found the broke a LY comparison DAX measure. There was an error with SAMEPERIODLASTYEAR. Do you know if this method is incompatible? As I am new to the platform, I am still trying to figure out what methods to use for the best, especially when it comes to date selections, interactions and calculations. Thanks
Great question. You'd use a calculation like below. Where you'd apply an ALLL function to the selection table, allowing the date function to ignore that slicer and retrieve the data from the previous year. Avt Tuition Same Period Last Year = CALCULATE ( [Avg Tuition in State], ALL ( 'MTD/QTD/YTD Selection' ), SAMEPERIODLASTYEAR ( 'Calendar'[Month Year] ) )
Hey @Havens Consulting ! Back again with another question, would you have any insight into creating a current and next quarter button into this list? would we have to designate a quarter end variable? Also, kinda stumped on how to show the next quarter in the manner as well.
Similar to the other periods you'd need to capture the start/end date of the current and next quarters. A good starting point is that I'd suggest exploring the STARTOFQUARTER functions to get some of the dates, and work your way from there :)
Okay, so i have the quarter start variable defined and that works perfect, yet my quarterend variable seems to be creating using returning two blank values for both the quarter start and quarter end variable. Code for QuarterEnd VAR QuarterEnd = Calculate(dateadd(ENDOFQUARTER('Sales Order Fact'[Transaction Date]), +2,MONTH), MONTH('Sales Order Fact'[Transaction Date]) +2 = MONTH(TodayDate)) what i am doing wrong here?
This is great! Question - can I use this in conjuction with a Date Slicer so I give my users the option for MTD/QTD/YTD but also let them pick a specific date range?
You can! But you'd want to clear one slicer, before using the other slicer. Sometimes I see people with a bookmark toggle to select between the two slicers. One visible at a time, So toggling between them switches one for the other, and clears the filter at the same time
This is exactly what I was looking for! Two questions: 1) is it possible to apply this selector so that you can do YoY calculations or would you need 2 selectors? 2) How did you get around the multiple column error?
Hi there, if you're looking to calculate YoY values, that would be more suited for a DAX measure itself. Otherwise as you mentioned you would need to have the slicer show both years in the filter. I'm not sure what you mean about the "multiple column error"
@@derrickkuria4206 unfortunately I'm not sure I fully understanding your data model. Any measure based on a fact table, with a a relationship to your calendar table/time intelligence table will filter by these date periods. With any model, you need a measure per column you want to aggregate, regardless of custom calendar periods.
Great video, is there a way to create a time intelligent slicer with options, years,month,day that whenever one is selected the x axis of a chart will change. For example I have data across many dates spanning 1 year. I want the slicer when I select month to show a bar graph with months for that year in the bottom on the x axis. If I select the day option, the bar graph will change to all the days on the x axis.
Thanks! This is one of those cases where it's easier to do in DAX. There's a way to do it in Power Query, but I don't have any code base or template for it.
Great stuff, just starter learning Power BI and have got lots of use from your videos! For this particular example since I cannot use my DAX Calendar. I use the production Data date column which vad not a complete date range. I want a complete date range since I will use the slicer on multiple production datasets. How would you solve it?
It’s honestly a best practice to have a calendar table with complete year ranges. I’d consider first updating your calendar table to that. I’m also releasing a video soon showing how to grab dates for the time intelligence table from your calendar table with avoiding the circular dependency error. That’s coming in a few weeks 🙂
@@HavensConsulting when I try to use my main Calendar(Date) table I receive error due to a circular referencing. When I use a separate Date table (created Only for this purpose) in the VAR statements and the main Calendar table in the union min and max function it all works. But I wonder if I can set ut up in a different to get rid of the second date table.
@@CHQAQC I have a video in a few weeks that solves the circular dependency issue. It'll be based on this article :) www.sqlbi.com/articles/avoiding-circular-dependency-errors-in-dax/
It's perfect! But i have a problem. I can't calculate last year values this way. I want to see the last year vs this year sales. For example when I click MTD, filtered only this year dates. So I can't see the last year dates. How can I see the both at the same page?
Cool approach! Is it correct that it will only work for this year? There is no way to analyze previous year in the same report page? Very often I need to show this and previous years next to each other.
Hi there, good question. It can work for any custom date range you’d like. You just need to specify the start / stop dates in each of the union’d tables in the DAX code. I just chose current year since that is most common
Hi there, good question. It can work for any custom date range you’d like. You just need to specify the start / stop dates in each of the union’d tables in the DAX code. I just chose current year since that is most common
Marat Abdullaev hi there, to achieve something like that wouldn’t be a slicer. You’d create a DAX measure for current year, and one for previous. Then place them side by side in a visual
@@havenscr Hi Reid, I was able to get the year 2020 but I don't know how to it for the previous years, could you please share the formula ? Many thanks for the awesome content in your channel
You definitely can! The previous period measure would need to ignore the filter from the time intelligence table. So if you used an ALL function to ignore that table. Then either DATEADD or SAMEPERIODLASTYEAR would let you see the previous period when used in the measure.
SAMEPERIODLASTYEAR function is returning blank value when we have data filtered to MTD/QTD etc. Any idea why is this and what is the alternate way to do sameperiod last year calculations.
Without taking a look at the PBIX and seeing the actual rows in the fact table, model, and relationships. I wouldn't have enough info to diagnose this. But there's a good chance this is being caused by missing data (rows) for the prior period either on the DIM calendar table, or FACT table.
Can i show the last year sales too when i click the MTD and YTD Slicers, please need help here, kind of lost here, i have CY and LY sales in a separate table connected to a common calendar.
@@HavensConsulting i tried it but i got trouble getting the desired results, my 2023 and 2024 sales are located in different tables and i have connected those sales datas dates with calendar table. When i do this formula i am getting filtered only this year sales till date and last years disappear’s. How will i see last years sales from jan to till date along with current years. This is what i want. Need your assistance here 🥹.
For the start date in a "rolling 12 month" range you can use the TODAY() function - 365 to get the 12 month prior start date, and the end date is TODAY()
Unfortunately PowerPivot doesn't allow DAX generated tables, or bi-directional relationships. It is possible there but you'd need to build it in PQ, then also have a bridge table to allow for this many-to-many connection between this date table and your calendar table
thank you for the response. I'm trying to first build out a reporting tool in the free version of Power Pivot/Power Query for a smaller department as our company is heavy users of Tableau and it will take a while to adopt Power BI, if at all. Would you be able to share how best to do this in Power Pivot alone sometime? Thank you for your consideration@@HavensConsulting
Hi, thanks for this informative video. I have one quick question here, what is the data column which you used for calender date which you showed in top right corner? It will very helpful for me if I know the exact column. Thanks... Keep doing this kind of complex solution videos...
Hi there! The workbook is available for download from our Blog Files page where you can see that and any other logic we applied for the video! Link to the Blog Files page is found in our description :)
Hi Great video, thank you for sharing this. Quick question, how can I show MTD/QTD/YTD values of current year and previous year on the same Chart without creating three different measures for previous year. thank you.
You could use the measure below to fetch the prior year data for any filter on the date selection slicer CALCULATE ( [Measure], ALL ( 'MTD/QTD/YTD Selection' ), SAMEPERIODLASTYEAR ( 'Calendar'[Month Year] ) )
How is the date range slicer returning dates as per the MTD, QTD, YTD selection? What is the measure against the date range slicer so that it fetches dates as per the monthly, quarterly or yearly selection?
Maybe this has already been asked, and I've done a lot of researching and testing but still cannot solve it. These slicers are awesome, but my measures for SAMEPERIODLASTYEAR or other parallel periods don't work this these slicers. I have a bunch of KPI Scorecards that compare this fiscal year to last fiscal year. How can I adjust the measures for last fiscal year to work with these slicers? I tried adding an additional date dimension table, but now my values for LFY mirror TFY.
You can use the "Get Data" option in Excel to connect to a published Power BI semantic model. Which would let you use this time intelligence table as a slicer with a PivotTable or chart. All leveraging this model and data :)
HI, it's a really good video. Is there any method to change the refresh date time dynamically with the max value of the date in the calendar table? So, we can add a date slicer for users to pick different reference points for MTD/YTD/YTD. I have tried to change max("refresh Datetime"[DateTime]) to max('Calendar'[Date] ) but there is no luck. Thanks
Hi Christopher, I'm not sure I understand the whole question. But if you're wanting to make MTD, QTD, YTD "dynamic" with date selection. Then here's a post that can help with that :) community.fabric.microsoft.com/t5/Desktop/YTD-with-Date-selection/m-p/1295338#M564537
Hi Reid. You had mentioned that we could also perform this calculation using the Power Query option. In terms of performance: Using DAX is faster than Power Query? Is there any threshold or a best practices saying for example up, to xxx million rows use DAX and more than xxx use Power Query? Thanks and have a good one!
Good question. There isn't an absolute answer for either. I do find that for a date table like this the M code would be a bit more complicated, which is why I used DAX instead. Also, with a time intelligence calculated table the end result will never be more than thousands of rows. I'd be hard pressed to see a million row plus date table. So I think DAX in this case is fine. Typically I use DAX generated tables when date logic or aggregations of values are involved, and for other table and column transformations that require actual reshaping of tables (filtering rows, removing columns, unpivoting, grouping, etc.) then I use Power Query. Hope this helps!
Great video. One question, what happen when the transactional data doesn't have the first date of the range?. For example, I don't have data for January 1st (Holiday), and the YTD range start on January 2nd. Is there any way to solve it?
What an awesome solution. Thank you . I have a single date column on my data. I noticed you have “Month Year” on your demonstration data table. How do I get the “Month Year?” Can’t wait to try this.
Hi there, I didn't create that column myself. It just happens that the public data I use is at the month, not the day level. It's treated no differently than a standard "date" column that would have day level data. Linked from a Fact Table to a Date Dimension table. Hope this helps!
Hi, can you please help me out? I am getting the error : A date column containing duplicate dates was specified in the call to function STARTOFYEAR. This is not supported . The current operation was cancelled because another operation in the transaction failed
Thank you so much for your response. I actually have a table which has columns for the current year, last year, and YOY figures. What I want to do is with the time intelligence filters, and last year values to be populated as well, can we do that??@@HavensConsulting
@@anupajayakody you should be able to write measures for that. Leveraging DATEADD and other time intelligence functions. Otherwise that's something that would honestly require me to look at the file, and workshop with a client! We do offer those services to help clients build out metrics. Feel free to check our website for consulting services if you're interested. :) www.havensconsulting.net/contact-and-support
Having issues getting the date range slicer to show values outside of what selection you choose. Example, when I click MTD, all dates outside of MTD are not showing up on date range slicer. When I click YTD, I can’t go to anything prior to this year using the date range slicer. I want to still be able to click on any date using the date range slicer. Any ideas?
Hi Erin, great question. Based on the design of this the time intelligence table filters the calendar table. To use a date range slicer outside of the time intelligence filter you'd need to clear the other slicer first. Otherwise a significant model redesign is required to use them both at the same time and not interfere with each other.
Hello Reid, I am getting error message of "The start date or end date in calendar function can not be blank value" when I ran DAX formula for Time Intelligence Selection.. I have managed to check my relationship as this is absolutely working well. Need help to fix this error.
Hi there, a few things can cause a blank value error. If you can provide more detail or a copy of the file please email me at info@havensconsulting.net
@@HavensConsulting Hi , thanks for quick response. Your videos are just awesome that gets me glued every time I watch it... Unfortunately I am having customer data into the file and I am not in position but what be appreciated if you can provide troubleshooting on potential scenarios with solution may be top ones..thx for support..
@@kpkdeep1118 it looks like you're trying to build the table off a calendar table, or table that has BLANKS in the date column for the MIN/MAX functions. In order for this to work you'll need to point those A) at a column that doesn't have blanks, or B) before import filter out blanks from that column using the query editor. But again it is tricky to know the exact issue without looking at the file. If you're having issues you're welcome to grab a consulting hour from us, more info can be found on our contact page. www.havensconsulting.net/contact-and-support
Hi Reid, this technique seems very helpful. However, I keep getting the error- "A date column containing duplicate dates was specified in the call to function 'STARTOFYEAR'. This is not supported." Same thing for startofmonth as well. I think this might have to do with the fact that my data is on a day, hours and seconds level and the demo data shows at the month and year level. However, i can not get around on how to fix this error. Any suggestions will be appreciated. Thank you
I was having this issue as well, so for anyone who is still trying to solve this try: 1. Create a new date column from your old date column: NewDateCol = INT('Table'[OldDateCol]) 2. Format this new column as Date 3. Make sure that NewDateCol is selected in the Calendar to Data relationship instead of OldDateCol
Of course! Whatever the slicer selection is when you save + publish is the default. You can also add any range you want. You just need to define the start stop ranges for your logic. You can see I added a bunch of other ranges as well in some of my follow up videos. th-cam.com/video/xCHnjzgfLG8/w-d-xo.html
Hey mate, great video! the only thing I am unsure is to have that Bi-directional relationship.... hmm I will test it. Are you certain you don´t get wierd results? Or can you have the direction to be from MTD/QTD/YTD Selection table to calendar table only and still work?
Hi there, good question. There should always be considerations and testing done when it comes to bi-directional relationships. And the consultant answer of course to your question of "does it get weird results?" is "it depends". In the setup I have for the model, and the visuals I'm using, there won't be any weird issues for the end user. For these reports the user wants to filter dates ONLY by the MTD/QTD/YTD slicer, nothing else. In this case these relationships do exactly that. Your other question, can you change the direction to go from the Selection table to the Calendar table only. The answer to that is no sadly. You can pick a direction of single or both. If you pick single, the filters will naturally only travel from the calendar table to the selection table, which is opposite of the direction we want. Which is the reason I need the bi-directional turned on. You can see that in this screenshot here. www.screencast.com/t/SJ2Rhgvjbk
@@havenscr mate thanks for the promptly answer, I gave it a shot to a particular model I have and it worked properly and nicely, although I will keep an eye on it becuase bi-directional relationships should be treated with caution. As for the single direction, thats correct and it does not work and hence you need to go with Bidirectional. I look forward to the part 2 video (USERELATIONSHIP function). Congrats! Thanks for sharing this trick, and cheers from Lima, Perú.
Thank you for this great video, this is exactly what I was looking for for my client ! Though when trying to implement it, I get an error "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value." : would anyone know what I've been doing wrong ? Thank you so much !
@@HavensConsulting Hi ! Thank you so much for your quick answer : I've actually used the exact same formula you provide, though I made the mistake of creating a first date column and not creating the table from the modelling tab. Now it works perfectly :) Thanks !
do you know how to solve this problem on the DAX measure? "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value"
Top search result for that error should help. www.sqlgene.com/2019/04/30/dax-error-the-expression-refers-to-multiple-columns-multiple-columns-cannot-be-converted-to-a-scalar-value/
Really liked your creativity!!! Just one question, I'm new to DAX... how did you create that table/measure which has selection and dates column. I tried that by manually entering data in column as MTD, QTD, YTD and tried adding another column with calendar date... CALENDAR(DATE(2020, 1, 1), DATE(2020, 12, 31))....but I'm getting error saying ...."A table of multiple values was supplied where a single value was expected". Please suggest the correct way to do that. Thanks a lot.
Hi there, the easiest way is I'd recommend downloading the file from my Blog Files page, and copy/pasting the DAX code from there. You can find the link to my blog files in the video description above :)
Was able to get this to work, but it stopped my measures for SAMEPERIODLASTYEAR from working as well. Any tips on how to get that working alongside this?
@@HavensConsulting I was able to replicate your selection table, that part works great. I have a bi-directional relationship between the selection table and my calendar table. My calendar table also has a one to many relationship with another data table. I believe since the relationship between selection table and calendar table is bi-directional when using selection slicer it is filtering out the ability to use SAMEPERIODLASTYEAR measures that I've written, as related to the data table. Appreciate any assistance and thank you for sharing this video.
@@crispow4488 I added prior year data into my fact table in my demo file, and this measure worked in returning the same period last year: Measure Name = CALCULATE ( [Avg Tuition in State], ALL ( 'MTD/QTD/YTD Selection' ), SAMEPERIODLASTYEAR ( 'Calendar'[Month Year] ) )
Is this working fine, Same report I use multiple cards for current year and sameperiodlastyear I use this video I create table but I couldn't get last year data
Hii ! I want to show previous week data for e.g. today is 19th it should show 7 to 13 data what logic i need to add for this condition as week start date and end date
Hi there! I've actually done about 4 follow up videos to this series (links in the video description). If you still need help with custom date periods. Then I'd probably need some more context to create these custom date ranges for you. This is the exact type of consulting work we help our clients with! Feel free to book some time with us at our contact portal if you wish to purchase some consulting hours. www.havensconsulting.net/contact-and-support
Hi i have created the ytd selection table but when creating relation ship in modeling it throwing an error as circular dependency please help me to go through that thanks in advance
It's a bit tricky, since you can't point this DAX Generated table at your calendar table without creating a circular dependency error. Two solutions I can think of: A) if your calendar table is made in PQ, you can point this table's logic to the date column on that table. This would be my recommended option. Or option B) is create a separate disconnected DAX calendar table using the CALENDER function, same start/end date as your calendar table. Then hide it in your model, and point the date selection logic to that date field in there, since it's a continuous date range.
If the date from the data table is last date of previous month due to financial run and today date is not equal to that date, the MonthStart is returning blank value with error The start date or end date in Calendar function can not be Blank value. How to solve this?
Good question. As of today you can't update a DAX table or column with a filter or slicer selection The calculated values contained in the DAX Calculated table or column will only update during model refreshes.
Great question! You can download the PBIX file from our Blog Files page and it'll have the DAX measure in it. Each video has that link in the description and I'll also include it here. www.havensconsulting.net/blog-files
I keep having an issue where I am getting a "The start date or end date in Calendar function can not be Blank value". This is due to the fact that my data table doesn't always have data early on in the month which is causing the blank start as it can't find the actual month from today's date. This ends up breaking the entire dashboard as all of my visuals are reliant on this slicer. What is my best solution for working around this?
It's a bit tricky, since you can't point this DAX Generated table at your calendar table without creating a circular dependency error. Two solutions I can think of: A) if your calendar table is made in PQ, you can point this table's logic to the date column on that table. This would be my recommended option. Or option B) is create a seperate disconnected DAX calendar table using the CALENDER function, same start/end date as your calendar table. Then hide it in your model, and point the date selection logic to that date field in there, since it's a continuous date range.
You'd need to make a calculation group to do this then, A stored/imported table like in this scenario can't update the table data via a slicer selection, only model refresh. Instead you'd need to create a calculation group to do that, and each calc item would contain whatever relative date logic you'd want. It requires a few steps, and I don't have a video for this specifically, but hopefully this gets you started.
Hi Tina, you'd have a variable for WTD VAR WeekStart = CALCULATE(TodayDate - WEEKDAY(TodayDate,2), YEAR(Data[Month Year]) = YEAR(TodayDate) ) and a new part for the bottom union ADDCOLUMNS ( CALENDAR ( WeekStart, TodayDate ), "Selection", "WTD", "Sort", 4 ),
Is it possible to create a time intelligence slicer that looks at the past 30,60,90 days similar to what you have in this video? would you be able to make a video on that?
It will probably be a while before I'd get around to an update for that one. But I tried to built the file in a way to make it easy to modify. Just update each of those ranges I made to accommodate the dates you're mentioning above!
Hi, I have followed all the steps but getting an error while creating this table “the start date or end date in Calendar function can not be Blank value” and data is present in main table…please help me to resolve this…thanks!!
Great question. You can avoid this issue by using dates from the calendar table! I did a follow up showing how to do this. th-cam.com/video/JYywNHCKhMk/w-d-xo.html
When I try to create a relationship between calenderDim and MTD/QTD/YTD table it gives an error as a circular dependency. any idea how to solve this..?
@@andrewsmith1438 I might do another video on it in the future. But for now I've uploaded an alternative file in my Blog Files page where you can download a copy of the report with a FYTD selection. 😎
@@HavensConsulting That is fantastic. Works perfectly. Will try to apply this logic to show Fiscal QTD as well as Fiscal PYTD etc. Really helpful. Thanks again.
There isn't really. That table is only refreshed during model refreshes. The values wouldn't change when you would apply a filter from a slicer or the filters pane. Maybe someday we'll have that feature!
@@AshokSharma-sd1nd you'd need to add a disconnected date table to your model of a list of dates, and then replace any value of TODAY() with something that grabs the slicer selection of "today" from this disconnected table. I'll make a note of doing a video about this sometime in the future. But these are the basic steps
@@HavensConsulting throwing error is start date or end date in calendar function can not be blank.however selected value from slicer showing correct in card visual.
Great video, spot on for what I needed. Question - I've a date table using calendar formula but when I link it to this dateselection table I'm getting a circular reference. My calendar table is linked to the min and max from my sales tables, and date selection I'm trying to link to my calendar table on date. Worst case I could link to sales table but not my preferred option. Any ideas?
Hi Sir...I tried with the code I'm getting below error in PBI file "The start date or end date in Calendar function can not be Blank value". and Blog is also not working plse help in this Sir
Could you provide more information on what you mean by “blog not working” With your blank value error that means that the DAX is attempting to fetch a date from a table that doesn’t contain a row for that specific date it’s looking for and returning a blank value. There are some solutions to similar issues to this posted on other comments as well on this video. Hope this helps!
I've a problem with that technique, though it works great for some visual, it doesn't work for table for example, the calendar is not filtered (or maybe I did something wrong)
With this relationship method, how can we calculate the Previous YTD, Previous MTD, Previous QTD etc(in one measure)...but we need to have only YTD, MTD & QTD in selection
Good question. For any Time Intelligence measure that requires reaching outside of the date filter slicer, you'd need to apply an ALL function. Here's an example measure using SAMEPERIODLASTYEAR() CALCULATE ( [Avg Tuition in State], ALL ( 'MTD/QTD/YTD Selection' ), SAMEPERIODLASTYEAR ( 'Calendar'[Month Year] ) )
Hi Vinay, last month to date wouldn't work well because if you were in March past the 28th of the month, it wouldn't be able to increment forward and give an accurate PMTD anymore. As example
Any date range can be specified. Just need to specify that in the logic of when the start/end date would be for any range. E.g. MTD, or FYTD, FQTD, etc.
Ho do we do this from a Date selected from a Date Slicer. i.e. How do I pick the MTD/ YTD/ WTD from a random selected from a Date Slicer? Say 30th Apr 2021
With this practice of a DAX table, you can't recalculate the date ranges in that table with a slicer. You'd need to do your process entirely just with the date filters being generated and applied inside of the DAX measures.
Good question! Each time you open the file in Power BI Desktop. TODAY()'s date will change to whatever the current date and time is. Having a Power Query refresh table enforces that the "current" refresh date time doesn't change unless you refresh that table or the entire model. You can open the query editor and see the query steps I built to make that.
Hi there, without seeing your model I don't have enough information to know the issue with your model. Can you provide the formula you used for your table?
This looks amazing solution. However I was wondering what if we have multiple fact table and all the fact tables have date column. In your scenario it was only 1 fact table.
@@HavensConsulting Thanks . My question was in case of multiple fact tables, what column(from which fact) I should take for creating calculations. My different fact tables have different level of Dates.
Good questions. You can’t have a single slicer with selections for MTD, QTD, YTD, PYTD, FYTD, etc. on a single column if you put them in a date table. You’d need a binary yes/no flag column for each. With a table like this you can build out those selections in a single column and have a slicer selection for it. 🙂
Here's an article explaining that error well :) www.sqlgene.com/2019/04/30/dax-error-the-expression-refers-to-multiple-columns-multiple-columns-cannot-be-converted-to-a-scalar-value/
Throwing out a hail Mary here as this video is two years old but for some reason, my date column goes all the way back to the 1900's. What could I be doing wrong?
Hi there! That would mean you have a date field with a date value that far back. I'd check all your source date columns to see what the earliest date is. But if you're either using CALENDARAUTO or MIN on a date column that is returning a date that early, it would mean this.
This is a lovely time intelligence addition. I'll be adding this to one of my reports. Thanks
Brilliant solution to YTD MTD buttons. Thank you soooooooooooooooo much for the sharing.
You're welcome!
Excellent. Simple yet powerful approach.
For those, like me, that tried to make it work with WTD as well, i made it like this:
var WeekStart = CALCULATE(TodayDate-WEEKDAY(TodayDate,2),YEAR(Data[Data])=YEAR(TodayDate))
is it possible to get Last Week and last month formula
How did you enter the previous week?
Hey .... Nice! can you help in getting the ptd done in same way .... ex if the start of fiscal preiod is as below
fiscal_period_num fiscal_period_start_date fiscal_period_end_date
1 2023-01-29 2023-02-25
2 2023-02-26 2023-03-25
3 2023-03-26 2023-04-29
4 2023-04-30 2023-05-27
5 2023-05-28 2023-06-24
6 2023-06-25 2023-07-29
7 2023-07-30 2023-08-26
8 2023-08-27 2023-09-23
9 2023-09-24 2023-10-28
10 2023-10-29 2023-11-25
11 2023-11-26 2023-12-30
12 2022-12-25 2023-01-28
12 2023-12-31 2024-02-03
Hey there, a really well explained video. This was the first real DAX work I've done and I managed to follow it and create the functionality. I even used the prinicples to add a "Yesterday" in there as well!
Thanks! Yeah there's another great video follow up to this one too that talks about other time intelligence perspectives. th-cam.com/video/xCHnjzgfLG8/w-d-xo.html
Hi! thank you for this wonderful video! I have a little question, what is the information from Data[month year]? because I have a table with 12 dates (start year date 09/30/2019 and end year date 08/31/2020) but when I replace the information with my table it got an error : "The start date in Calendar function can not be later than the end date". Could you please let us know how can we replace the table month year?
Man i am like waiting restless for every video or ur's.
Glad to hear it :)
Nice job!. How do I modify the formula to accommodate YTD LY, MTD LY and QTD LY?
Hi, I fixed it! I've also adapted the code to use the latest date of the calendar table instead of today's date (as the dashboard might not be updated, it's just for best practice).
I've also added WTD and Yesterday's date (so it's a complete code in my opinion :) )
To briefly explain what I've done: I've extended the calendar to also display LY dates of the same period from YTD, QTD, MTD, WTD, Yesterday.
Please find my full code below.
Daily/WTD/MTD/QTD/YTD Selection =
VAR LatestDate = LASTDATE('dim Calendar'[Date]) - 1
VAR YearStart = CALCULATE(STARTOFYEAR('Fact GMC Sales'[Date]),YEAR('Fact GMC Sales'[Date])=YEAR(LatestDate) )
VAR QuarterStart = CALCULATE(STARTOFQUARTER('Fact GMC Sales'[Date]), QUARTER('Fact GMC Sales'[Date]) = QUARTER(LatestDate),YEAR('Fact GMC Sales'[Date])=YEAR(LatestDate) )
VAR MonthStart = CALCULATE(STARTOFMONTH('Fact GMC Sales'[Date]), MONTH('Fact GMC Sales'[Date]) = MONTH(LatestDate),YEAR('Fact GMC Sales'[Date])=YEAR(LatestDate) )
VAR WeekStart = CALCULATE(LatestDate-WEEKDAY(LatestDate, 2),YEAR('Fact GMC Sales'[Date])=YEAR(LatestDate))
VAR LY_LatestDate = SAMEPERIODLASTYEAR(LASTDATE('dim Calendar'[Date])) - 1
VAR LY_YearStart = CALCULATE(STARTOFYEAR('Fact GMC Sales'[Date]),YEAR('Fact GMC Sales'[Date])=YEAR(LY_LatestDate) )
VAR LY_QuarterStart = CALCULATE(STARTOFQUARTER('Fact GMC Sales'[Date]), QUARTER('Fact GMC Sales'[Date]) = QUARTER(LatestDate),YEAR('Fact GMC Sales'[Date])=YEAR(LY_LatestDate) )
VAR LY_MonthStart = CALCULATE(STARTOFMONTH('Fact GMC Sales'[Date]), MONTH('Fact GMC Sales'[Date]) = MONTH(LY_LatestDate),YEAR('Fact GMC Sales'[Date])=YEAR(LY_LatestDate) )
VAR LY_WeekStart = CALCULATE(LY_LatestDate-WEEKDAY(LY_LatestDate, 2),YEAR('Fact GMC Sales'[Date])=YEAR(LY_LatestDate) )
VAR Result =
UNION (
ADDCOLUMNS(
CALENDAR(YearStart, LatestDate),
"Selection", "YTD",
"SortId", 5
),
ADDCOLUMNS(
CALENDAR(LY_YearStart, LY_LatestDate),
"Selection", "YTD",
"SortId", 5
),
ADDCOLUMNS(
CALENDAR(QuarterStart, LatestDate),
"Selection", "QTD",
"SortId", 4
),
ADDCOLUMNS(
CALENDAR(LY_QuarterStart, LY_LatestDate),
"Selection", "QTD",
"SortId", 4
),
ADDCOLUMNS(
CALENDAR(MonthStart, LatestDate),
"Selection", "MTD",
"SortId", 3
),
ADDCOLUMNS(
CALENDAR(LY_MonthStart, LY_LatestDate),
"Selection", "MTD",
"SortId", 3
),
ADDCOLUMNS(
CALENDAR(WeekStart, LatestDate),
"Selection", "WTD",
"SortId", 2
),
ADDCOLUMNS(
CALENDAR(LY_WeekStart, LY_LatestDate),
"Selection", "WTD",
"SortId", 2
),
ADDCOLUMNS(
CALENDAR(LatestDate, LatestDate),
"Selection", "Yesterday",
"SortId", 1
),
ADDCOLUMNS(
CALENDAR(LY_LatestDate, LY_LatestDate),
"Selection", "Yesterday",
"SortId", 1
)
)
Return
Result
if you wanted to change the order of the items in the slicer so it shows YTD, QTD, MTD you can add a column to the table in the code and then sort the selections based on that. For Example - ADDCOLUMNS(
CALENDAR(YearStart, TodayDate),
"Selection", "YTD", "Order", 1)
Hi there, that's absolutely correct. Adding a custom sort column would allow you to control the slicer order if needed. Thanks for mentioning.
I would like to add previous Month to the selection + 3prior months
PreviousMonth = if current month is march, previous month would be 01/02/2023 - 28/02/2023
3 prior months : if current month is march, prior 3 months would be 01/12/2022 - 28/02/2023
Thank you for this video! Quite Impressive. I'm working on something, based on the video MTD/QTD/YTD I created the selection and the calculations seem to work fine. I had one question can we also compare with PMTD/PQTD/PYTD as well at the same time basis the MTD/YTD/QTD selections. Thanks
Thank you so much for this tutorial! Was very educative and well explained! Impressive work!
Nice use of relationship! I will definitely try this out. Normally I use the pattern with disconnected slicers and switch formulas so I'm excited to see this from a performance perspective. If the selection table can be calculated in PQ it can be stored in data flows for reuse in other models, but alot more fun crunching out a good DAX code ;)
Good question, Holly also asked a similar question in these comments last month. I'll copy/paste my answer to that here as well :)
It's a bit tricky, since you can't point this DAX Generated table at your calendar table without creating a circular dependency error. Two solutions I can think of: A) if your calendar table is made in PQ, you can point this table's logic to the date column on that table. This would be my recommended option. Or option B) is create a separate disconnected DAX calendar table using the CALENDER function, same start/end date as your calendar table. Then hide it in your model, and point the date selection logic to that date field in there, since it's a continuous date range.
@HavensConsulting thanks for this optimized option. But, I am facing the circular issue despite creating the date table in PQ. If that can be mitigated, it will reduce redundant measures
Super video, very helpful.
I have a question: what if I would like to select a month so to calculate YTD and MTD of that month, without using any formula, could I make it? Thank you.
Great video. Playing with this (as a newbie to PBI) I have recreated it in PQ but found the broke a LY comparison DAX measure. There was an error with SAMEPERIODLASTYEAR. Do you know if this method is incompatible? As I am new to the platform, I am still trying to figure out what methods to use for the best, especially when it comes to date selections, interactions and calculations. Thanks
Great question. You'd use a calculation like below. Where you'd apply an ALLL function to the selection table, allowing the date function to ignore that slicer and retrieve the data from the previous year.
Avt Tuition Same Period Last Year =
CALCULATE ( [Avg Tuition in State],
ALL ( 'MTD/QTD/YTD Selection' ),
SAMEPERIODLASTYEAR ( 'Calendar'[Month Year] )
)
Hey @Havens Consulting ! Back again with another question, would you have any insight into creating a current and next quarter button into this list? would we have to designate a quarter end variable? Also, kinda stumped on how to show the next quarter in the manner as well.
Similar to the other periods you'd need to capture the start/end date of the current and next quarters. A good starting point is that I'd suggest exploring the STARTOFQUARTER functions to get some of the dates, and work your way from there :)
Okay, so i have the quarter start variable defined and that works perfect, yet my quarterend variable seems to be creating using returning two blank values for both the quarter start and quarter end variable.
Code for QuarterEnd
VAR QuarterEnd = Calculate(dateadd(ENDOFQUARTER('Sales Order Fact'[Transaction Date]), +2,MONTH), MONTH('Sales Order Fact'[Transaction Date]) +2 = MONTH(TodayDate))
what i am doing wrong here?
If our date table has relationships both active and inactive to other tables how do we avoid a circular dependency?
Great Video @HavensConsulting
What if in this video, I want to add custom dates as well. How can we achieve that?
Check the description on this video to see follow up videos which hopefully helps answer your additional date range questions. :)
This is great! Question - can I use this in conjuction with a Date Slicer so I give my users the option for MTD/QTD/YTD but also let them pick a specific date range?
You can! But you'd want to clear one slicer, before using the other slicer. Sometimes I see people with a bookmark toggle to select between the two slicers. One visible at a time, So toggling between them switches one for the other, and clears the filter at the same time
This is exactly what I was looking for! Two questions: 1) is it possible to apply this selector so that you can do YoY calculations or would you need 2 selectors? 2) How did you get around the multiple column error?
Hi there, if you're looking to calculate YoY values, that would be more suited for a DAX measure itself. Otherwise as you mentioned you would need to have the slicer show both years in the filter. I'm not sure what you mean about the "multiple column error"
Is it possible to add a week to date selection to the panel?
Great practical solution Reid! Thank you! The Dax table using Union will be a nice add to my go-to tools, and faster calcs as a bonus!
Glad to hear it!
Wow kept it up and you will have a huge following soon!
Hi, Thanks for such a great video.
I have a doubt what if we have 2 fact tables, and pulling some of measure from it. Would that work in that case
As long as both Fact Tables are connected to this calendar table, absolutely!
@@HavensConsulting Thanks🙏
@@HavensConsulting that means we will write thew formula as many times as the tables, since the Fact table names are referenced here?
@@derrickkuria4206 unfortunately I'm not sure I fully understanding your data model. Any measure based on a fact table, with a a relationship to your calendar table/time intelligence table will filter by these date periods. With any model, you need a measure per column you want to aggregate, regardless of custom calendar periods.
Hii @@derrickkuria4206 , Did you find out the way if we 2 facts ??
Great video, is there a way to create a time intelligent slicer with options, years,month,day that whenever one is selected the x axis of a chart will change. For example I have data across many dates spanning 1 year. I want the slicer when I select month to show a bar graph with months for that year in the bottom on the x axis. If I select the day option, the bar graph will change to all the days on the x axis.
Great staff! Really helped me a lot.
Could you share the M code version of this? I think it would be slightly better for my case
Thanks! This is one of those cases where it's easier to do in DAX. There's a way to do it in Power Query, but I don't have any code base or template for it.
Great stuff, just starter learning Power BI and have got lots of use from your videos!
For this particular example since I cannot use my DAX Calendar. I use the production Data date column which vad not a complete date range. I want a complete date range since I will use the slicer on multiple production datasets. How would you solve it?
It’s honestly a best practice to have a calendar table with complete year ranges. I’d consider first updating your calendar table to that. I’m also releasing a video soon showing how to grab dates for the time intelligence table from your calendar table with avoiding the circular dependency error. That’s coming in a few weeks 🙂
@@HavensConsulting when I try to use my main Calendar(Date) table I receive error due to a circular referencing. When I use a separate Date table (created Only for this purpose) in the VAR statements and the main Calendar table in the union min and max function it all works. But I wonder if I can set ut up in a different to get rid of the second date table.
I will keep an eye open for that video :) which video editing tool do you use to produce your videos by the way?
@@CHQAQC I have a video in a few weeks that solves the circular dependency issue. It'll be based on this article :)
www.sqlbi.com/articles/avoiding-circular-dependency-errors-in-dax/
@@CHQAQC Camtasia 2021
It's perfect! But i have a problem. I can't calculate last year values this way. I want to see the last year vs this year sales. For example when I click MTD, filtered only this year dates. So I can't see the last year dates. How can I see the both at the same page?
Cool approach! Is it correct that it will only work for this year? There is no way to analyze previous year in the same report page? Very often I need to show this and previous years next to each other.
Hi there, good question. It can work for any custom date range you’d like. You just need to specify the start / stop dates in each of the union’d tables in the DAX code. I just chose current year since that is most common
Hi there, good question. It can work for any custom date range you’d like. You just need to specify the start / stop dates in each of the union’d tables in the DAX code. I just chose current year since that is most common
@@havenscr Thanks. But I still don't see how it would be possible to have MTD TY vs MTD LY next to each other in the same table?!
Marat Abdullaev hi there, to achieve something like that wouldn’t be a slicer. You’d create a DAX measure for current year, and one for previous. Then place them side by side in a visual
@@havenscr Hi Reid, I was able to get the year 2020 but I don't know how to it for the previous years, could you please share the formula ? Many thanks for the awesome content in your channel
Hi !! great Video , but can i use the same slicer to show YTD,QTD,MTD for previous period along with the Current in the same visual graph?
You definitely can! The previous period measure would need to ignore the filter from the time intelligence table. So if you used an ALL function to ignore that table. Then either DATEADD or SAMEPERIODLASTYEAR would let you see the previous period when used in the measure.
HI, amazing thanks for sharing. I subscribed to the blog but still can't download the file
Hi there, did you get the welcome email? That includes the password to the Blog Files page
SAMEPERIODLASTYEAR function is returning blank value when we have data filtered to MTD/QTD etc.
Any idea why is this and what is the alternate way to do sameperiod last year calculations.
Without taking a look at the PBIX and seeing the actual rows in the fact table, model, and relationships. I wouldn't have enough info to diagnose this. But there's a good chance this is being caused by missing data (rows) for the prior period either on the DIM calendar table, or FACT table.
Can i show the last year sales too when i click the MTD and YTD Slicers, please need help here, kind of lost here, i have CY and LY sales in a separate table connected to a common calendar.
Some of my later videos in this series address that. Check the YT description for links to the follow up videos :)
@@HavensConsulting thanks a lot, i was kind of struggling here. The video is so helping. Thanks for the quicker response 😃
@@HavensConsulting i tried it but i got trouble getting the desired results, my 2023 and 2024 sales are located in different tables and i have connected those sales datas dates with calendar table. When i do this formula i am getting filtered only this year sales till date and last years disappear’s. How will i see last years sales from jan to till date along with current years. This is what i want. Need your assistance here 🥹.
Good stuff. Would be very helpful with selling days to calculate Avg Daily Sales, MTD Sales, QTD SALES, and YTD Sales... Maybe include Units and POs 😀
Hey,
Any Idea if I want to add a MAT (rolling 12 months) slicer as well ? thanks a lot
For the start date in a "rolling 12 month" range you can use the TODAY() function - 365 to get the 12 month prior start date, and the end date is TODAY()
can this be done just in Power Pivot and Power Query? does it have to be in Power BI? thank you!
Unfortunately PowerPivot doesn't allow DAX generated tables, or bi-directional relationships. It is possible there but you'd need to build it in PQ, then also have a bridge table to allow for this many-to-many connection between this date table and your calendar table
thank you for the response. I'm trying to first build out a reporting tool in the free version of Power Pivot/Power Query for a smaller department as our company is heavy users of Tableau and it will take a while to adopt Power BI, if at all. Would you be able to share how best to do this in Power Pivot alone sometime? Thank you for your consideration@@HavensConsulting
Hi, thanks for this informative video.
I have one quick question here, what is the data column which you used for calender date which you showed in top right corner?
It will very helpful for me if I know the exact column.
Thanks... Keep doing this kind of complex solution videos...
Hi there! The workbook is available for download from our Blog Files page where you can see that and any other logic we applied for the video! Link to the Blog Files page is found in our description :)
Hi Great video, thank you for sharing this. Quick question, how can I show MTD/QTD/YTD values of current year and previous year on the same Chart without creating three different measures for previous year. thank you.
You could use the measure below to fetch the prior year data for any filter on the date selection slicer
CALCULATE ( [Measure],
ALL ( 'MTD/QTD/YTD Selection' ),
SAMEPERIODLASTYEAR ( 'Calendar'[Month Year] )
)
What if i want to create a WTD button. I tried, but there is no start of week DAX. Any suggestion?
var WeekStart = CALCULATE(TodayDate-WEEKDAY(TodayDate,2),YEAR(Data])=YEAR(TodayDate))
How is the date range slicer returning dates as per the MTD, QTD, YTD selection? What is the measure against the date range slicer so that it fetches dates as per the monthly, quarterly or yearly selection?
Good questoins. Check this video description for the parts 2, 3, 4, etc that should address your above questions. :)
Maybe this has already been asked, and I've done a lot of researching and testing but still cannot solve it. These slicers are awesome, but my measures for SAMEPERIODLASTYEAR or other parallel periods don't work this these slicers. I have a bunch of KPI Scorecards that compare this fiscal year to last fiscal year. How can I adjust the measures for last fiscal year to work with these slicers? I tried adding an additional date dimension table, but now my values for LFY mirror TFY.
If I wanted to do this in Excel's DAX would it work? if not how may I be able to accomplish this?
You can use the "Get Data" option in Excel to connect to a published Power BI semantic model. Which would let you use this time intelligence table as a slicer with a PivotTable or chart. All leveraging this model and data :)
Thank you!@@HavensConsulting
HI, it's a really good video. Is there any method to change the refresh date time dynamically with the max value of the date in the calendar table? So, we can add a date slicer for users to pick different reference points for MTD/YTD/YTD. I have tried to change max("refresh Datetime"[DateTime]) to max('Calendar'[Date] ) but there is no luck. Thanks
Hi Christopher, I'm not sure I understand the whole question. But if you're wanting to make MTD, QTD, YTD "dynamic" with date selection. Then here's a post that can help with that :)
community.fabric.microsoft.com/t5/Desktop/YTD-with-Date-selection/m-p/1295338#M564537
Hi Reid. You had mentioned that we could also perform this calculation using the Power Query option. In terms of performance: Using DAX is faster than Power Query? Is there any threshold or a best practices saying for example up, to xxx million rows use DAX and more than xxx use Power Query? Thanks and have a good one!
Good question. There isn't an absolute answer for either. I do find that for a date table like this the M code would be a bit more complicated, which is why I used DAX instead. Also, with a time intelligence calculated table the end result will never be more than thousands of rows. I'd be hard pressed to see a million row plus date table. So I think DAX in this case is fine. Typically I use DAX generated tables when date logic or aggregations of values are involved, and for other table and column transformations that require actual reshaping of tables (filtering rows, removing columns, unpivoting, grouping, etc.) then I use Power Query. Hope this helps!
Great video. One question, what happen when the transactional data doesn't have the first date of the range?. For example, I don't have data for January 1st (Holiday), and the YTD range start on January 2nd. Is there any way to solve it?
What an awesome solution. Thank you .
I have a single date column on my data. I noticed you have “Month Year” on your demonstration data table. How do I get the “Month Year?”
Can’t wait to try this.
Hi there, I didn't create that column myself. It just happens that the public data I use is at the month, not the day level. It's treated no differently than a standard "date" column that would have day level data. Linked from a Fact Table to a Date Dimension table. Hope this helps!
Reid Havens : Okay. Much obliged for the prompt answer. Will continue to anticipate for your videos.
Hi, can you please help me out? I am getting the error : A date column containing duplicate dates was specified in the call to function STARTOFYEAR. This is not supported . The current operation was cancelled because another operation in the transaction failed
How can we adjust this to also show the same period last year figures populated with the current year figures
Check the video description for other videos in this series. SAMEPERIODLASTYEAR is addressed in those
Thank you so much for your response. I actually have a table which has columns for the current year, last year, and YOY figures. What I want to do is with the time intelligence filters, and last year values to be populated as well, can we do that??@@HavensConsulting
@@anupajayakody you should be able to write measures for that. Leveraging DATEADD and other time intelligence functions. Otherwise that's something that would honestly require me to look at the file, and workshop with a client! We do offer those services to help clients build out metrics. Feel free to check our website for consulting services if you're interested. :)
www.havensconsulting.net/contact-and-support
Having issues getting the date range slicer to show values outside of what selection you choose. Example, when I click MTD, all dates outside of MTD are not showing up on date range slicer. When I click YTD, I can’t go to anything prior to this year using the date range slicer. I want to still be able to click on any date using the date range slicer. Any ideas?
Hi Erin, great question. Based on the design of this the time intelligence table filters the calendar table. To use a date range slicer outside of the time intelligence filter you'd need to clear the other slicer first. Otherwise a significant model redesign is required to use them both at the same time and not interfere with each other.
Hello Reid, I am getting error message of "The start date or end date in calendar function can not be blank value" when I ran DAX formula for Time Intelligence Selection.. I have managed to check my relationship as this is absolutely working well. Need help to fix this error.
Hi there, a few things can cause a blank value error. If you can provide more detail or a copy of the file please email me at info@havensconsulting.net
@@HavensConsulting Hi , thanks for quick response. Your videos are just awesome that gets me glued every time I watch it... Unfortunately I am having customer data into the file and I am not in position but what be appreciated if you can provide troubleshooting on potential scenarios with solution may be top ones..thx for support..
@@kpkdeep1118 it looks like you're trying to build the table off a calendar table, or table that has BLANKS in the date column for the MIN/MAX functions. In order for this to work you'll need to point those A) at a column that doesn't have blanks, or B) before import filter out blanks from that column using the query editor. But again it is tricky to know the exact issue without looking at the file. If you're having issues you're welcome to grab a consulting hour from us, more info can be found on our contact page. www.havensconsulting.net/contact-and-support
Hi Reid, this technique seems very helpful. However, I keep getting the error- "A date column containing duplicate dates was specified in the call to function 'STARTOFYEAR'. This is not supported." Same thing for startofmonth as well. I think this might have to do with the fact that my data is on a day, hours and seconds level and the demo data shows at the month and year level. However, i can not get around on how to fix this error. Any suggestions will be appreciated. Thank you
I was having this issue as well, so for anyone who is still trying to solve this try:
1. Create a new date column from your old date column:
NewDateCol = INT('Table'[OldDateCol])
2. Format this new column as Date
3. Make sure that NewDateCol is selected in the Calendar to Data relationship instead of OldDateCol
Does it work well in the case of a leap year or lead day (like 29 February 2024)?
Can you default the slicer to MTD?
Also can you add an option for prior year?
Of course! Whatever the slicer selection is when you save + publish is the default. You can also add any range you want. You just need to define the start stop ranges for your logic. You can see I added a bunch of other ranges as well in some of my follow up videos. th-cam.com/video/xCHnjzgfLG8/w-d-xo.html
Hey mate, great video! the only thing I am unsure is to have that Bi-directional relationship.... hmm I will test it. Are you certain you don´t get wierd results? Or can you have the direction to be from MTD/QTD/YTD Selection table to calendar table only and still work?
Hi there, good question. There should always be considerations and testing done when it comes to bi-directional relationships. And the consultant answer of course to your question of "does it get weird results?" is "it depends". In the setup I have for the model, and the visuals I'm using, there won't be any weird issues for the end user. For these reports the user wants to filter dates ONLY by the MTD/QTD/YTD slicer, nothing else. In this case these relationships do exactly that. Your other question, can you change the direction to go from the Selection table to the Calendar table only. The answer to that is no sadly. You can pick a direction of single or both. If you pick single, the filters will naturally only travel from the calendar table to the selection table, which is opposite of the direction we want. Which is the reason I need the bi-directional turned on. You can see that in this screenshot here. www.screencast.com/t/SJ2Rhgvjbk
@@havenscr mate thanks for the promptly answer, I gave it a shot to a particular model I have and it worked properly and nicely, although I will keep an eye on it becuase bi-directional relationships should be treated with caution. As for the single direction, thats correct and it does not work and hence you need to go with Bidirectional. I look forward to the part 2 video (USERELATIONSHIP function). Congrats!
Thanks for sharing this trick, and cheers from Lima, Perú.
Thank you for this great video, this is exactly what I was looking for for my client !
Though when trying to implement it, I get an error "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value." : would anyone know what I've been doing wrong ?
Thank you so much !
If you can provide the full DAX expression here, that would help!
@@HavensConsulting Hi !
Thank you so much for your quick answer : I've actually used the exact same formula you provide, though I made the mistake of creating a first date column and not creating the table from the modelling tab.
Now it works perfectly :)
Thanks !
@@carolinechan1118 glad to hear it got fixed!
do you know how to solve this problem on the DAX measure? "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value"
Top search result for that error should help.
www.sqlgene.com/2019/04/30/dax-error-the-expression-refers-to-multiple-columns-multiple-columns-cannot-be-converted-to-a-scalar-value/
Really liked your creativity!!! Just one question, I'm new to DAX... how did you create that table/measure which has selection and dates column. I tried that by manually entering data in column as MTD, QTD, YTD and tried adding another column with calendar date... CALENDAR(DATE(2020, 1, 1), DATE(2020, 12, 31))....but I'm getting error saying ...."A table of multiple values was supplied where a single value was expected". Please suggest the correct way to do that. Thanks a lot.
Hi there, the easiest way is I'd recommend downloading the file from my Blog Files page, and copy/pasting the DAX code from there. You can find the link to my blog files in the video description above :)
Was able to get this to work, but it stopped my measures for SAMEPERIODLASTYEAR from working as well. Any tips on how to get that working alongside this?
Unfortunately I don't have enough information to know, without looking at the model and the measures in more detail.
@@HavensConsulting I was able to replicate your selection table, that part works great. I have a bi-directional relationship between the selection table and my calendar table. My calendar table also has a one to many relationship with another data table. I believe since the relationship between selection table and calendar table is bi-directional when using selection slicer it is filtering out the ability to use SAMEPERIODLASTYEAR measures that I've written, as related to the data table.
Appreciate any assistance and thank you for sharing this video.
@@crispow4488 I added prior year data into my fact table in my demo file, and this measure worked in returning the same period last year:
Measure Name =
CALCULATE ( [Avg Tuition in State],
ALL ( 'MTD/QTD/YTD Selection' ),
SAMEPERIODLASTYEAR ( 'Calendar'[Month Year] )
)
Is this working fine,
Same report I use multiple cards for current year and sameperiodlastyear
I use this video I create table but I couldn't get last year data
I have dates for 4 years like from 2018 to 2022, at the place of today var what should I need to use.
Hi there, I'm not sure I understand the question. Are you just wanting a relative four year window from day going back 4 years?
Hii ! I want to show previous week data for e.g. today is 19th it should show 7 to 13 data what logic i need to add for this condition as week start date and end date
Hi there! I've actually done about 4 follow up videos to this series (links in the video description).
If you still need help with custom date periods. Then I'd probably need some more context to create these custom date ranges for you. This is the exact type of consulting work we help our clients with! Feel free to book some time with us at our contact portal if you wish to purchase some consulting hours.
www.havensconsulting.net/contact-and-support
great technique
Hi i have created the ytd selection table but when creating relation ship in modeling it throwing an error as circular dependency please help me to go through that thanks in advance
Good question. A few others have asked similar in the comments here as well. I'll provide that response here. :)
It's a bit tricky, since you can't point this DAX Generated table at your calendar table without creating a circular dependency error. Two solutions I can think of: A) if your calendar table is made in PQ, you can point this table's logic to the date column on that table. This would be my recommended option. Or option B) is create a separate disconnected DAX calendar table using the CALENDER function, same start/end date as your calendar table. Then hide it in your model, and point the date selection logic to that date field in there, since it's a continuous date range.
If the date from the data table is last date of previous month due to financial run and today date is not equal to that date, the MonthStart is returning blank value with error The start date or end date in Calendar function can not be Blank value. How to solve this?
I managed to solve it by taking MONTH ( Data[Month Year] ) = MONTH ( MAX ( Data[Month Year] ) )
Is there a way to add week to this.. current week?
Hi, how can you make this dynamic so that the YTD, MTD. QTD will be based on a selected date and not the max date.
Good question. As of today you can't update a DAX table or column with a filter or slicer selection The calculated values contained in the DAX Calculated table or column will only update during model refreshes.
AWESOME
How could i generate a dynamic table of Current/YTD/MAT with multiple selections of year month slicer?
Hi Moh, I'm not entirely sure I understand the request. Could you elaborate on the scenario or perhaps you could provide a screenshot of the concept?
how did you create this dynamic date title?
Great question! You can download the PBIX file from our Blog Files page and it'll have the DAX measure in it. Each video has that link in the description and I'll also include it here. www.havensconsulting.net/blog-files
I keep having an issue where I am getting a "The start date or end date in Calendar function can not be Blank value". This is due to the fact that my data table doesn't always have data early on in the month which is causing the blank start as it can't find the actual month from today's date. This ends up breaking the entire dashboard as all of my visuals are reliant on this slicer. What is my best solution for working around this?
It's a bit tricky, since you can't point this DAX Generated table at your calendar table without creating a circular dependency error. Two solutions I can think of: A) if your calendar table is made in PQ, you can point this table's logic to the date column on that table. This would be my recommended option. Or option B) is create a seperate disconnected DAX calendar table using the CALENDER function, same start/end date as your calendar table. Then hide it in your model, and point the date selection logic to that date field in there, since it's a continuous date range.
@@HavensConsulting Thank you! I had to create a new DAX Calendar but it did fix this issue!
I had the same problem looking at historical data with no today date. Ended up using max(date[date]) as alternative to today.
How can i make this dynamic based on selected date from slicer?
Could you elaborate on what you mean by dynamic?
@@HavensConsulting I want to replace todaydate by lastdate from dateslicer. based on the selected date from slicer will change WTD,MTD and YTD.
You'd need to make a calculation group to do this then, A stored/imported table like in this scenario can't update the table data via a slicer selection, only model refresh.
Instead you'd need to create a calculation group to do that, and each calc item would contain whatever relative date logic you'd want. It requires a few steps, and I don't have a video for this specifically, but hopefully this gets you started.
@@havenscr Thanks. Finally I found which I had been searching for, after a long time. It was not possible without your help.
Hello, how would I update to include WTD (Week to Date and LWK (Last Week)?
Hi Tina, you'd have a variable for WTD
VAR WeekStart = CALCULATE(TodayDate - WEEKDAY(TodayDate,2), YEAR(Data[Month Year]) = YEAR(TodayDate) )
and a new part for the bottom union
ADDCOLUMNS (
CALENDAR ( WeekStart, TodayDate ),
"Selection", "WTD",
"Sort", 4
),
@@HavensConsulting Thank you, I'm getting a circular dependency error
@@tinaflemons4022 th-cam.com/video/JYywNHCKhMk/w-d-xo.html this will help. 😊
Is it possible to create a time intelligence slicer that looks at the past 30,60,90 days similar to what you have in this video? would you be able to make a video on that?
It will probably be a while before I'd get around to an update for that one. But I tried to built the file in a way to make it easy to modify. Just update each of those ranges I made to accommodate the dates you're mentioning above!
Hi, I have followed all the steps but getting an error while creating this table “the start date or end date in Calendar function can not be Blank value” and data is present in main table…please help me to resolve this…thanks!!
Great question. You can avoid this issue by using dates from the calendar table! I did a follow up showing how to do this. th-cam.com/video/JYywNHCKhMk/w-d-xo.html
Power BI will no longer allow me to create the relationship due to circular dependency. How can I fix this?
There's a video link in the description of this video that should solve that issue for you. Part 5 of this series :)
@@HavensConsulting Thank you, that worked.
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.
I’ve not discovered a way to feed a dynamic date into the year end date field for that DAX function.
hello. Does this work for Fiscal years?My Year starts in April...
You'd just need to adjust the VAR StartDate to the beginning of your Fiscal Year :)
When I try to create a relationship between calenderDim and MTD/QTD/YTD table it gives an error as a circular dependency.
any idea how to solve this..?
th-cam.com/video/JYywNHCKhMk/w-d-xo.html I have a video addressing just this :)
@@HavensConsulting thanks will definitely watch.
what would be the solution if my fiscal year starts from August and ends with July? How do I generate the YTD,QTD,MTD Slicer?
Check the video description for parts 2, 3, and 4 which address other date and fiscal considerations :)
This is a great solution. One thing is can you please advise how to declare a July fiscal year?
Are you wanting a selection for FYTD?
@@HavensConsulting that would be great.
@@andrewsmith1438 I might do another video on it in the future. But for now I've uploaded an alternative file in my Blog Files page where you can download a copy of the report with a FYTD selection. 😎
@@HavensConsulting That is fantastic. Works perfectly. Will try to apply this logic to show Fiscal QTD as well as Fiscal PYTD etc. Really helpful. Thanks again.
is there a way to use dynamic date instead of today () in today date??
There isn't really. That table is only refreshed during model refreshes. The values wouldn't change when you would apply a filter from a slicer or the filters pane. Maybe someday we'll have that feature!
@@HavensConsulting Can we update selected date from slicer in replace of today date to make WTD, MTD and YTD dynamic. Pls help.
@@AshokSharma-sd1nd you'd need to add a disconnected date table to your model of a list of dates, and then replace any value of TODAY() with something that grabs the slicer selection of "today" from this disconnected table. I'll make a note of doing a video about this sometime in the future. But these are the basic steps
@@HavensConsulting throwing error is start date or end date in calendar function can not be blank.however selected value from slicer showing correct in card visual.
Great video, spot on for what I needed. Question - I've a date table using calendar formula but when I link it to this dateselection table I'm getting a circular reference.
My calendar table is linked to the min and max from my sales tables, and date selection I'm trying to link to my calendar table on date. Worst case I could link to sales table but not my preferred option.
Any ideas?
Fix to that in this follow up video :)
www.havensconsulting.net/blog-and-media/improving-the-time-intelligence-slicer-avoiding-circular-dependencies
How could this be adapted for Fiscal Years that do not start with January?
There's additional links in the description of additional videos showing how to deal with fiscal :)
I have following error named "The start date or end date in Calendar function can not be Blank value.". Please help.
You need to point that function (MIN/MAX) to a date column that doesn't have any blank values in it.
Hi Great video, but I get this error "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."
That error usually means you're referencing a full column when a single value is needed
If you can copy/paste the DAX formula here I can take a look
Hi Sir...I tried with the code I'm getting below error in PBI file "The start date or end date in Calendar function can not be Blank value". and Blog is also not working plse help in this Sir
Could you provide more information on what you mean by “blog not working”
With your blank value error that means that the DAX is attempting to fetch a date from a table that doesn’t contain a row for that specific date it’s looking for and returning a blank value. There are some solutions to similar issues to this posted on other comments as well on this video. Hope this helps!
I've a problem with that technique, though it works great for some visual, it doesn't work for table for example, the calendar is not filtered (or maybe I did something wrong)
Without looking at the model I don't have enough information to know what would be causing your issue.
With this relationship method, how can we calculate the Previous YTD, Previous MTD, Previous QTD etc(in one measure)...but we need to have only YTD, MTD & QTD in selection
Good question. For any Time Intelligence measure that requires reaching outside of the date filter slicer, you'd need to apply an ALL function. Here's an example measure using SAMEPERIODLASTYEAR()
CALCULATE ( [Avg Tuition in State],
ALL ( 'MTD/QTD/YTD Selection' ),
SAMEPERIODLASTYEAR ( 'Calendar'[Month Year] )
)
Can you please help with Last month MTD, Last Week WTD
Hi Vinay, last month to date wouldn't work well because if you were in March past the 28th of the month, it wouldn't be able to increment forward and give an accurate PMTD anymore. As example
@@HavensConsulting cool thank you, what about just start of the last month to end of the last month and the same for week.
But I have a lot of data tables, can I still use this?
As long as they’re connected to the same calendar table, yes. 🙂
@@HavensConsulting then which date data from my data tables should I use with the dax formula? Any of them? As long as they are connected?
@@lazydrain you could use the CALENDARAUTO function which scans all date columns and grabs the earliest and latest dates
Can you achieve this for financial month/years? e.g. April to March
Any date range can be specified. Just need to specify that in the logic of when the start/end date would be for any range. E.g. MTD, or FYTD, FQTD, etc.
Ho do we do this from a Date selected from a Date Slicer. i.e. How do I pick the MTD/ YTD/ WTD from a random selected from a Date Slicer? Say 30th Apr 2021
With this practice of a DAX table, you can't recalculate the date ranges in that table with a slicer. You'd need to do your process entirely just with the date filters being generated and applied inside of the DAX measures.
In the example file, why is there a "Refresh DateTime" table and how did you create this... why can't DAX just reference today with TODAY()
Good question! Each time you open the file in Power BI Desktop. TODAY()'s date will change to whatever the current date and time is. Having a Power Query refresh table enforces that the "current" refresh date time doesn't change unless you refresh that table or the entire model. You can open the query editor and see the query steps I built to make that.
Why is my MTD showing all the dates similar to YTD?
Hi there, without seeing your model I don't have enough information to know the issue with your model. Can you provide the formula you used for your table?
Great video. Can you post pbix for this too.
Hi there, certainly. It’s already posted in the blog files section of my website. The link to that is in the description of my video. Hope this helps!
Reid Havens Thanks Reid. You videos are easy to follow .
@@decentmendreams much appreciated, thank you. :)
Where is the part2 of this video?
This looks amazing solution. However I was wondering what if we have multiple fact table and all the fact tables have date column. In your scenario it was only 1 fact table.
As long as your Date Dimension table is connected to each fact table's date column. This solution would work for those as well. 🙂
@@HavensConsulting Thanks . My question was in case of multiple fact tables, what column(from which fact) I should take for creating calculations. My different fact tables have different level of Dates.
Suggestion, why are you using complex CALCULATE() and not just something like VAR YearStart = STARTOFYEAR(TODAY())
Good questions. You can’t have a single slicer with selections for MTD, QTD, YTD, PYTD, FYTD, etc. on a single column if you put them in a date table. You’d need a binary yes/no flag column for each. With a table like this you can build out those selections in a single column and have a slicer selection for it. 🙂
@@HavensConsulting Yes, you are correct, I realized that as soon as I asked the question :)
It Says "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."
Here's an article explaining that error well :) www.sqlgene.com/2019/04/30/dax-error-the-expression-refers-to-multiple-columns-multiple-columns-cannot-be-converted-to-a-scalar-value/
Throwing out a hail Mary here as this video is two years old but for some reason, my date column goes all the way back to the 1900's. What could I be doing wrong?
Hi there! That would mean you have a date field with a date value that far back. I'd check all your source date columns to see what the earliest date is. But if you're either using CALENDARAUTO or MIN on a date column that is returning a date that early, it would mean this.
Is it relevant even in 2023 or there's simpler approach available