Great video Havens Consulting 😊.. i have a question, so if I need next quarter data, would it be +1 instead of the -1.
@macrocosmique4 жыл бұрын
I had the idea to use buttons instead of the slicer itself, so that for each selection the hierarchy level changes as well, pretty cool, thanks a lot for this video!!
@MJ-vk8zv3 жыл бұрын
Great stuff Reid. What I'd like to add is rolling periods - MAT (12 Months), 6 Months, etc, however I don't see a straight-forward way of doing it. I can add -1 or -2 to the months on the variable, but run into trouble when it crosses 2 calendar years. Can advise or post an example?
@HavensConsulting3 жыл бұрын
Hi Matt, the month offset should allow you to create any trailing month range without much difficulty regardless of year. Could you provide more detail of how it is breaking?
@MJ-vk8zv3 жыл бұрын
@@HavensConsulting Cheers Reid. Just sent you an email as it's a bit too much to put in here. Thanks for getting back.
@richardh9999911 ай бұрын
Hi, I wasn't able to find the code from this video in your blog or anywhere else. Please help, to save me trying to copy it. Also, it would be helpful if you showed how you created the Dax entry. Is it a Table, a measure, a script or what?
@HavensConsulting11 ай бұрын
All our PBIX files can be downloaded here :) www.havensconsulting.net/blog-files
@HavensConsulting11 ай бұрын
Part 1 of this video series shows how I use a DAX generated table to make these custom calendar tables, all links to those are in the video description of this video series :)
@lakshmikanthparvathapuram12504 жыл бұрын
Could you pls explain..what is the dax formula for date range card and how it works based on the MTD YTD and other selections
@PandemicLightroom4 жыл бұрын
However with this solution I'm still not able to show current year and previous year side by side on the same visual as I realized with this "calendar type" solution SAMEPERIODLASTYEAR formula stop working. Any solution for that?
@HavensConsulting4 жыл бұрын
If you're wanting to show current and prior year together on a visual, that's usually best handled with DAX where you have a measure for Sales, and a measure for prior year sales, using SAMEPERIODLASTYEAR, DATEADD, etc.
@akhilreddy82204 жыл бұрын
@@HavensConsulting I am having the same issue. Even with measures using dateadd or sameperiodlastyear you can’t show sales amount and sales amount LY for a selection you make from the date period table. For some reason it only works with 1:1 on dateperiods not with many:1 bi-directional to the date table. Can you please post a video/ solution if you are able to solve this please
@HavensConsulting2 жыл бұрын
@@akhilreddy8220 You could 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] ) )
@sky3gpp3 жыл бұрын
Hi, I used this time intelligence slicer, it is really cool. However there is one issue, when I need to measure same period last year number, it is not working. Not sure if this is the DAX issue or I made something wrong. COuld you please explain it? THanks.
@HavensConsulting3 жыл бұрын
Apply an ALL filter against the time intelligence table and the calculation will work with same period last year 🙂
@sky3gpp3 жыл бұрын
@@HavensConsulting thanks. It is working now.
@HavensConsulting3 жыл бұрын
@@sky3gpp Excellent!
@macrocosmique4 жыл бұрын
Hi, I only have trouble with (okviz) sparklines : when I switch from this intelligent slicer to a normal date slicer (using bookmarks) sparklines have trouble displaying the right dates. I may also have to use show/hide one sparkline per slicer type
@damionc2 жыл бұрын
Havens Consulting. Can the variable for TodayDate be based on a selection from the user and not the actual date today? This is very good and would save you creating multiple measure but can it be more dynamic where the user selects the date? I know calculated columns and calculated tables are evaluated once when the data is first loaded and are static until the data is refreshed so what I am asking may be impossible. But in the real world users want to select the period and so it would be good to have mtd ytd slicer without it being connected to todays date. Otherwise we are back to creating multiple measures and using a switch statement.
@HavensConsulting2 жыл бұрын
Sadly today measures can't update column values. Only model refreshes for the most part can. You can create a calculation group those for these sets of date logic that is anchored to a slicer selection that would work!
@damionc2 жыл бұрын
@@HavensConsulting Thank you for your reply. I will look at calculation groups but the problem with them is that they often times don't work well with custom visuals when it comes to formatting.
@sue_bayes4 жыл бұрын
I really like this but am coming up with an issue where the Calendar date can't contain a blank value because the fact table that I'm using for the data table doesn't have a continuous date range, therefore granularity has to be at month level assuming sales in each month. Also to use previous mtd, etc, need to have at least three years of data.
@denxx61264 жыл бұрын
Thx... How did you create „Date Range Card? Custom Visual?
@HavensConsulting4 жыл бұрын
Hi there, good question. It is just a single value card and some custom DAX, if you're a blog subscriber you can download the PBIX file from my Blog Files page. You'll be able to see the visual used, and the DAX formula in the file. Hope this helps! www.havensconsulting.net/blog-files
@andrevioti4 жыл бұрын
@@HavensConsulting Could you please show how are you calculating the "Average Admission Rate"?
@mohamedaboobackersiddique73224 жыл бұрын
hi reid, will this technique can be applied to custom 445 calendar ?
@HavensConsulting4 жыл бұрын
Hi Mohamed, for any custom periods you would need to set that in the ranges for the DAX generated table.
@andrevioti4 жыл бұрын
Havens Consulting Could you please show how are you calculating the "Average Admission Rate"?
@HavensConsulting4 жыл бұрын
Hi there, do you mean where I got the data from, or the DAX measure? The measure itself just uses the AVERAGE() function against the data table. You can also download the file from our Blog Files page. Link in the description.
@gavinlin564 жыл бұрын
Great stuff! But why my sorting doesn't work, it is alphabeti now?
@tantinytanjung97693 жыл бұрын
Hi I want to modify to list very last month and this is my DAX Var LastMonthStart = CALCULATE(DATEADD(STARTOFMONTH(CL_Prod[Date_ID]), -1, MONTH), YEAR(CL_Prod[Date_ID]) = YEAR(PYTodayDate), MONTH(CL_Prod[Date_ID])-1 = MONTH(PYTodayDate), DAY(CL_Prod[Date_ID])= DAY(PYTodayDate)) But it give blank result, is there any I missing out ?Thank
@HavensConsulting3 жыл бұрын
Usually when it's returning BLANK the date you're trying to filter to doesn't actually exist as a row on the table. Is the date you're looking for an actual row on CL_Prod?
@muhammadusman72724 жыл бұрын
If we need Last 3 Months data, then what is Dax query. Like we just need Sep Oct and Nov data. Please help
@HavensConsulting4 жыл бұрын
Within the file you can download for this video, you can adapt the DAX measure for that time period. Add a variable for a start date of the start of the month three months ago as your start date. For the end date declare a variable that calculates the end of month for the prior month. That would give you a previous 3 month date window to then use in the slicer/filter.
@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 How do I adjust the dax code to achieve this?
@dineshkumarnagarajan2004 жыл бұрын
Awesome Great work! I'm getting an error "Expression specified in query is not valid expression", any solution plz ?
@HavensConsulting4 жыл бұрын
I'm sorry but I don't have enough information to help with that.
@hermesjrd4 жыл бұрын
Thanks a lot. Pretty neat...
@system_user_vp4 жыл бұрын
thank you! perfectly!
@gopigopichand43794 жыл бұрын
You are awesome..👌👌🥰
@mshparber4 жыл бұрын
February 29 might be an issue...
@HavensConsulting4 жыл бұрын
Hi Michael, with any previous comparisons that can certainly come up. February 29th prior year will become March 1st in DAX though.
@adityakalra73834 жыл бұрын
Hi! I hope you don't share the data. Please can you share data for practice purpose? Thanks!
@HavensConsulting4 жыл бұрын
Hi there. The file is available to download from my blog files page if you're a subscriber. Hope this helps! www.havensconsulting.net/blog-files