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?
@matheusbergamo42954 жыл бұрын
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))
@virgovinay3 жыл бұрын
is it possible to get Last Week and last month formula
@tinaflemons40222 жыл бұрын
How did you enter the previous week?
@shripadv2607 Жыл бұрын
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
@katiawei6817 Жыл бұрын
Brilliant solution to YTD MTD buttons. Thank you soooooooooooooooo much for the sharing.
@HavensConsulting Жыл бұрын
You're welcome!
@satishkumardommeti82824 жыл бұрын
Excellent. Simple yet powerful approach.
@atifmir74094 жыл бұрын
This is a lovely time intelligence addition. I'll be adding this to one of my reports. Thanks
@raphaelokoye43104 жыл бұрын
Nice job!. How do I modify the formula to accommodate YTD LY, MTD LY and QTD LY?
@andrefreitas15722 жыл бұрын
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
@adamsidebottom50234 жыл бұрын
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!
@HavensConsulting4 жыл бұрын
Thanks! Yeah there's another great video follow up to this one too that talks about other time intelligence perspectives. kzbin.info/www/bejne/rnSrn52wnMt_fZo
@kurzashane4 жыл бұрын
If our date table has relationships both active and inactive to other tables how do we avoid a circular dependency?
@kurzashane4 жыл бұрын
Is it possible to add a week to date selection to the panel?
@mayurpawar49842 жыл бұрын
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
@DDDMFZ4 жыл бұрын
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.
@spuddgg2 жыл бұрын
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.
@HavensConsulting2 жыл бұрын
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 :)
@spuddgg2 жыл бұрын
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?
@elmiguel10223 жыл бұрын
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?
@sabiretutardemir4 жыл бұрын
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?
@twinklesethi2127 Жыл бұрын
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?
@HavensConsulting Жыл бұрын
Good questoins. Check this video description for the parts 2, 3, 4, etc that should address your above questions. :)
@90s_HipHop4 жыл бұрын
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
@HavensConsulting2 жыл бұрын
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] ) )
@jayalmendarez4 жыл бұрын
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.
@BearOnHisBike2 жыл бұрын
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)
@HavensConsulting2 жыл бұрын
Hi there, that's absolutely correct. Adding a custom sort column would allow you to control the slicer order if needed. Thanks for mentioning.
@sindibad6024 ай бұрын
HI, amazing thanks for sharing. I subscribed to the blog but still can't download the file
@HavensConsulting4 ай бұрын
Hi there, did you get the welcome email? That includes the password to the Blog Files page
@gavinlin564 жыл бұрын
What if i want to create a WTD button. I tried, but there is no start of week DAX. Any suggestion?
@matheusbergamo42954 жыл бұрын
var WeekStart = CALCULATE(TodayDate-WEEKDAY(TodayDate,2),YEAR(Data])=YEAR(TodayDate))
@abhilashg2879 ай бұрын
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.
@HavensConsulting9 ай бұрын
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.
@philipposorfanos2099 ай бұрын
Does it work well in the case of a leap year or lead day (like 29 February 2024)?
@MelissaCornfield4 жыл бұрын
Is there a way to add week to this.. current week?
@lmentzer41202 жыл бұрын
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?
@HavensConsulting2 жыл бұрын
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
@carlosescutia97789 ай бұрын
If I wanted to do this in Excel's DAX would it work? if not how may I be able to accomplish this?
@HavensConsulting9 ай бұрын
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 :)
@carlosescutia97789 ай бұрын
Thank you!@@HavensConsulting
@junaidmalik95934 жыл бұрын
Man i am like waiting restless for every video or ur's.
@HavensConsulting4 жыл бұрын
Glad to hear it :)
@omarmendoza-q5q10 ай бұрын
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"
@HavensConsulting10 ай бұрын
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/
@michaels1813 Жыл бұрын
Can you default the slicer to MTD? Also can you add an option for prior year?
@HavensConsulting Жыл бұрын
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. kzbin.info/www/bejne/rnSrn52wnMt_fZo
@christamucci30014 жыл бұрын
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.
@praparnamoharana9579 Жыл бұрын
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
@gowiththevind Жыл бұрын
Great Video @HavensConsulting What if in this video, I want to add custom dates as well. How can we achieve that?
@HavensConsulting Жыл бұрын
Check the description on this video to see follow up videos which hopefully helps answer your additional date range questions. :)
@zaheeratabassum68962 жыл бұрын
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?
@HavensConsulting2 жыл бұрын
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.
@luisedugarridosoares4 жыл бұрын
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!
@HavensConsulting4 жыл бұрын
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!
@atlrising Жыл бұрын
can this be done just in Power Pivot and Power Query? does it have to be in Power BI? thank you!
@HavensConsulting Жыл бұрын
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
@atlrising Жыл бұрын
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
@raghavgupta60322 жыл бұрын
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
@HavensConsulting2 жыл бұрын
As long as both Fact Tables are connected to this calendar table, absolutely!
@raghavgupta60322 жыл бұрын
@@HavensConsulting Thanks🙏
@derrickkuria42062 жыл бұрын
@@HavensConsulting that means we will write thew formula as many times as the tables, since the Fact table names are referenced here?
@HavensConsulting2 жыл бұрын
@@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.
@preethama-sc1on Жыл бұрын
Hii @@derrickkuria4206 , Did you find out the way if we 2 facts ??
@tikku172 жыл бұрын
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.
@HavensConsulting2 жыл бұрын
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] ) )
@motlover2 жыл бұрын
Hey, Any Idea if I want to add a MAT (rolling 12 months) slicer as well ? thanks a lot
@HavensConsulting2 жыл бұрын
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()
@christopherchu1823 Жыл бұрын
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
@HavensConsulting Жыл бұрын
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
@anupajayakody Жыл бұрын
How can we adjust this to also show the same period last year figures populated with the current year figures
@HavensConsulting Жыл бұрын
Check the video description for other videos in this series. SAMEPERIODLASTYEAR is addressed in those
@anupajayakody Жыл бұрын
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
@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
@samnad49726 ай бұрын
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.
@HavensConsulting6 ай бұрын
Some of my later videos in this series address that. Check the YT description for links to the follow up videos :)
@samnad49726 ай бұрын
@@HavensConsulting thanks a lot, i was kind of struggling here. The video is so helping. Thanks for the quicker response 😃
@samnad49725 ай бұрын
@@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 🥹.
@mannilyngo31352 жыл бұрын
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.
@HavensConsulting2 жыл бұрын
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.
@kannanm6039 Жыл бұрын
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...
@HavensConsulting Жыл бұрын
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 :)
@carolinechan11182 жыл бұрын
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 !
@HavensConsulting2 жыл бұрын
If you can provide the full DAX expression here, that would help!
@carolinechan11182 жыл бұрын
@@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 !
@HavensConsulting2 жыл бұрын
@@carolinechan1118 glad to hear it got fixed!
@mohshalsh59652 жыл бұрын
How could i generate a dynamic table of Current/YTD/MAT with multiple selections of year month slicer?
@HavensConsulting2 жыл бұрын
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?
@crispow44883 жыл бұрын
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?
@HavensConsulting3 жыл бұрын
Unfortunately I don't have enough information to know, without looking at the model and the measures in more detail.
@crispow44883 жыл бұрын
@@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.
@HavensConsulting3 жыл бұрын
@@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] ) )
@yuvrajbugdikattekar91587 ай бұрын
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
@erinlindquist17852 жыл бұрын
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?
@HavensConsulting2 жыл бұрын
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.
@hemantsethiya1554 Жыл бұрын
I have dates for 4 years like from 2018 to 2022, at the place of today var what should I need to use.
@HavensConsulting Жыл бұрын
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?
@CHQAQC3 жыл бұрын
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?
@HavensConsulting3 жыл бұрын
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 🙂
@CHQAQC3 жыл бұрын
@@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.
@CHQAQC3 жыл бұрын
I will keep an eye open for that video :) which video editing tool do you use to produce your videos by the way?
@HavensConsulting3 жыл бұрын
@@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/
@HavensConsulting3 жыл бұрын
@@CHQAQC Camtasia 2021
@zchase66312 жыл бұрын
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?
@zchase66312 жыл бұрын
I managed to solve it by taking MONTH ( Data[Month Year] ) = MONTH ( MAX ( Data[Month Year] ) )
@jonathandupuis94743 жыл бұрын
Thank you so much for this tutorial! Was very educative and well explained! Impressive work!
@PHDL-h5e Жыл бұрын
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
@tinaflemons40222 жыл бұрын
Hello, how would I update to include WTD (Week to Date and LWK (Last Week)?
@HavensConsulting2 жыл бұрын
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 ),
@tinaflemons40222 жыл бұрын
@@HavensConsulting Thank you, I'm getting a circular dependency error
@HavensConsulting2 жыл бұрын
@@tinaflemons4022 kzbin.info/www/bejne/gIrcqIF-eLCbg80 this will help. 😊
@TauTauTau14 жыл бұрын
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?
@HavensConsulting4 жыл бұрын
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!
@rutujaatre1111 ай бұрын
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
@HavensConsulting11 ай бұрын
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
@lxmisrs Жыл бұрын
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?
@HavensConsulting Жыл бұрын
Check the video description for parts 2, 3, and 4 which address other date and fiscal considerations :)
@shreyakeshari8851 Жыл бұрын
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!!
@HavensConsulting Жыл бұрын
Great question. You can avoid this issue by using dates from the calendar table! I did a follow up showing how to do this. kzbin.info/www/bejne/gIrcqIF-eLCbg80
@uzmarat4 жыл бұрын
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.
@havenscr4 жыл бұрын
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
@havenscr4 жыл бұрын
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
@uzmarat4 жыл бұрын
@@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?!
@havenscr4 жыл бұрын
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
@rayleighsilvers25554 жыл бұрын
@@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
@kpkdeep11184 жыл бұрын
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.
@HavensConsulting4 жыл бұрын
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
@kpkdeep11184 жыл бұрын
@@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..
@HavensConsulting4 жыл бұрын
@@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
@AshokSharma-sd1nd5 ай бұрын
How can i make this dynamic based on selected date from slicer?
@HavensConsulting5 ай бұрын
Could you elaborate on what you mean by dynamic?
@AshokSharma-sd1nd5 ай бұрын
@@HavensConsulting I want to replace todaydate by lastdate from dateslicer. based on the selected date from slicer will change WTD,MTD and YTD.
@havenscr5 ай бұрын
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.
@AshokSharma-sd1nd5 ай бұрын
@@havenscr Thanks. Finally I found which I had been searching for, after a long time. It was not possible without your help.
@sravankumar57633 жыл бұрын
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
@HavensConsulting3 жыл бұрын
Good question. A few others have asked similar in the comments here as well. I'll provide that response here. :)
@HavensConsulting3 жыл бұрын
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.
@venkateshthammisetty78454 жыл бұрын
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.
@HavensConsulting4 жыл бұрын
I’ve not discovered a way to feed a dynamic date into the year end date field for that DAX function.
@alamsa014 жыл бұрын
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
@shleybulock2 жыл бұрын
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
@derrickkuria42062 жыл бұрын
hello. Does this work for Fiscal years?My Year starts in April...
@HavensConsulting2 жыл бұрын
You'd just need to adjust the VAR StartDate to the beginning of your Fiscal Year :)
@tinaflemons2637 ай бұрын
Power BI will no longer allow me to create the relationship due to circular dependency. How can I fix this?
@HavensConsulting7 ай бұрын
There's a video link in the description of this video that should solve that issue for you. Part 5 of this series :)
@tinaflemons2637 ай бұрын
@@HavensConsulting Thank you, that worked.
@zachdigan46664 жыл бұрын
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?
@HavensConsulting4 жыл бұрын
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"
@judycalvo51273 жыл бұрын
How could this be adapted for Fiscal Years that do not start with January?
@HavensConsulting3 жыл бұрын
There's additional links in the description of additional videos showing how to deal with fiscal :)
@vishalnasre12513 жыл бұрын
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..?
@HavensConsulting3 жыл бұрын
kzbin.info/www/bejne/gIrcqIF-eLCbg80 I have a video addressing just this :)
@vishalnasre12513 жыл бұрын
@@HavensConsulting thanks will definitely watch.
@sahmedalee2 жыл бұрын
how did you create this dynamic date title?
@HavensConsulting2 жыл бұрын
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
@hanygeorge44244 жыл бұрын
is there a way to use dynamic date instead of today () in today date??
@HavensConsulting4 жыл бұрын
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-sd1nd6 ай бұрын
@@HavensConsulting Can we update selected date from slicer in replace of today date to make WTD, MTD and YTD dynamic. Pls help.
@HavensConsulting6 ай бұрын
@@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
@AshokSharma-sd1nd5 ай бұрын
@@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.
@nareshsingh-ez5ex4 жыл бұрын
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.
@havenscr4 жыл бұрын
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 :)
@Doffskii4 жыл бұрын
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 ;)
@HavensConsulting3 жыл бұрын
Good question, Holly also asked a similar question in these comments last month. I'll copy/paste my answer to that here as well :)
@HavensConsulting3 жыл бұрын
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.
@nehachak Жыл бұрын
@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
@spilledgraphics4 жыл бұрын
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?
@havenscr4 жыл бұрын
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
@spilledgraphics4 жыл бұрын
@@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ú.
@hollyzipperstein55843 жыл бұрын
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?
@HavensConsulting3 жыл бұрын
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.
@hollyzipperstein55843 жыл бұрын
@@HavensConsulting Thank you! I had to create a new DAX Calendar but it did fix this issue!
@stevenstanford8618 Жыл бұрын
I had the same problem looking at historical data with no today date. Ended up using max(date[date]) as alternative to today.
@krzysiekhyla96864 жыл бұрын
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
@HavensConsulting4 жыл бұрын
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.
@andrewwatson276711 ай бұрын
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?
@HavensConsulting11 ай бұрын
Fix to that in this follow up video :) www.havensconsulting.net/blog-and-media/improving-the-time-intelligence-slicer-avoiding-circular-dependencies
@virgovinay3 жыл бұрын
Can you please help with Last month MTD, Last Week WTD
@HavensConsulting3 жыл бұрын
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
@virgovinay3 жыл бұрын
@@HavensConsulting cool thank you, what about just start of the last month to end of the last month and the same for week.
@seymourburt2046 Жыл бұрын
Hi Great video, but I get this error "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."
@HavensConsulting Жыл бұрын
That error usually means you're referencing a full column when a single value is needed
@HavensConsulting Жыл бұрын
If you can copy/paste the DAX formula here I can take a look
@mwaltercpa4 жыл бұрын
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!
@havenscr4 жыл бұрын
Glad to hear it!
@sarva04833 жыл бұрын
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
@HavensConsulting3 жыл бұрын
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] ) )
@romasejal4 жыл бұрын
I have following error named "The start date or end date in Calendar function can not be Blank value.". Please help.
@HavensConsulting4 жыл бұрын
You need to point that function (MIN/MAX) to a date column that doesn't have any blank values in it.
@datavisuk12254 жыл бұрын
Can you achieve this for financial month/years? e.g. April to March
@HavensConsulting4 жыл бұрын
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.
@lazydrain2 жыл бұрын
But I have a lot of data tables, can I still use this?
@HavensConsulting2 жыл бұрын
As long as they’re connected to the same calendar table, yes. 🙂
@lazydrain2 жыл бұрын
@@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?
@havenscr2 жыл бұрын
@@lazydrain you could use the CALENDARAUTO function which scans all date columns and grabs the earliest and latest dates
@zuhebkhan69732 жыл бұрын
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
@HavensConsulting2 жыл бұрын
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!
@jimbobeny3 жыл бұрын
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()
@HavensConsulting3 жыл бұрын
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.
@decentmendreams4 жыл бұрын
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.
@havenscr4 жыл бұрын
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!
@decentmendreams4 жыл бұрын
Reid Havens : Okay. Much obliged for the prompt answer. Will continue to anticipate for your videos.
@andrewsmith14383 жыл бұрын
This is a great solution. One thing is can you please advise how to declare a July fiscal year?
@HavensConsulting3 жыл бұрын
Are you wanting a selection for FYTD?
@andrewsmith14383 жыл бұрын
@@HavensConsulting that would be great.
@HavensConsulting3 жыл бұрын
@@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. 😎
@andrewsmith14383 жыл бұрын
@@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.
@nuwanmenuka3 жыл бұрын
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
@HavensConsulting3 жыл бұрын
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.
@jackeroo754 жыл бұрын
Wow kept it up and you will have a huge following soon!
@macrocosmique4 жыл бұрын
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)
@HavensConsulting4 жыл бұрын
Without looking at the model I don't have enough information to know what would be causing your issue.
@handymangamer2383 жыл бұрын
Suggestion, why are you using complex CALCULATE() and not just something like VAR YearStart = STARTOFYEAR(TODAY())
@HavensConsulting3 жыл бұрын
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. 🙂
@handymangamer2383 жыл бұрын
@@HavensConsulting Yes, you are correct, I realized that as soon as I asked the question :)
@parthtiwari68022 жыл бұрын
Why is my MTD showing all the dates similar to YTD?
@HavensConsulting2 жыл бұрын
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?
@csrajaraman4 жыл бұрын
Where is the part2 of this video?
@Silvioamartinezjr4 жыл бұрын
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 😀
@adnanalam72004 жыл бұрын
Great video. Can you post pbix for this too.
@havenscr4 жыл бұрын
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!
@decentmendreams4 жыл бұрын
Reid Havens Thanks Reid. You videos are easy to follow .
@havenscr4 жыл бұрын
@@decentmendreams much appreciated, thank you. :)
@bhuruu4 жыл бұрын
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.
@HavensConsulting4 жыл бұрын
As long as your Date Dimension table is connected to each fact table's date column. This solution would work for those as well. 🙂
@bhuruu4 жыл бұрын
@@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.
@chronostinger20113 жыл бұрын
It Says "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."
@HavensConsulting3 жыл бұрын
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/
@AzizRehman01113 жыл бұрын
great technique
@RajShankar664 жыл бұрын
AWESOME
@kastenivkimbo5347 Жыл бұрын
Is it relevant even in 2023 or there's simpler approach available
@jonathanminutillo48973 жыл бұрын
and the code? what a difficult to find someone who shared complete data.
@HavensConsulting3 жыл бұрын
The entire code and workbook is available in my Blog Files page. Link in the description :)
@jonathanminutillo48973 жыл бұрын
@@HavensConsulting sorry man, I am your slave now. 😀