Creating a MTD/QTD/YTD Time Intelligence Slicer in Power BI 

Thanks! Yeah there's another great video follow up to this one too that talks about other time intelligence perspectives. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-xCHnjzgfLG8.html

Wow, thanks!

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

You're welcome!

Glad to hear it :)

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

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.

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"

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] ) )

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

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

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!

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

Glad to hear it!

Fix to that in this follow up video :) www.havensconsulting.net/blog-and-media/improving-the-time-intelligence-slicer-avoiding-circular-dependencies

Hi there, that's absolutely correct. Adding a custom sort column would allow you to control the slicer order if needed. Thanks for mentioning.

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 :)

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?

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] ) )

Check the description on this video to see follow up videos which hopefully helps answer your additional date range questions. :)

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 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 :)

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

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

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

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

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!

Good questoins. Check this video description for the parts 2, 3, 4, etc that should address your above questions. :)

var WeekStart = CALCULATE(TodayDate-WEEKDAY(TodayDate,2),YEAR(Data])=YEAR(TodayDate))

Without looking at the model I don't have enough information to know what would be causing your issue.

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.

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()

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.

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 ??

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. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-xCHnjzgfLG8.html

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.

I’ve not discovered a way to feed a dynamic date into the year end date field for that DAX function.

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.

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

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] ) )

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/

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 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?

I managed to solve it by taking MONTH ( Data[Month Year] ) = MONTH ( MAX ( Data[Month Year] ) )

If both fact tables are connected to the same date table that this time intelligence slicer is connected to. Then both will get filtered the same way.

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?

Check the video description for parts 2, 3, and 4 which address other date and fiscal considerations :)

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

You need to point that function (MIN/MAX) to a date column that doesn't have any blank values in it.

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.

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

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

Great question. You can avoid this issue by using dates from the calendar table! I did a follow up showing how to do this. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-JYywNHCKhMk.html

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

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

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.

There's additional links in the description of additional videos showing how to deal with fiscal :)

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.

ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-JYywNHCKhMk.html I have a video addressing just this :)

@@HavensConsulting thanks will definitely watch.

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

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. :)

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

You'd just need to adjust the VAR StartDate to the beginning of your Fiscal Year :)

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

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

Could you elaborate on what you mean by dynamic?

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.

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 ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-JYywNHCKhMk.html this will help. 😊

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.

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.

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!

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 :)

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.

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/

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.

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

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?

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.