Additional Date Period Filters for the Time Intelligence Slicer

Рет қаралды 7,436

Havens Consulting

Күн бұрын

Пікірлер: 46

@aswathiad Жыл бұрын

Great video Havens Consulting 😊.. i have a question, so if I need next quarter data, would it be +1 instead of the -1.

@macrocosmique 4 жыл бұрын

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-vk8zv 3 жыл бұрын

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?

@HavensConsulting 3 жыл бұрын

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-vk8zv 3 жыл бұрын

@@HavensConsulting Cheers Reid. Just sent you an email as it's a bit too much to put in here. Thanks for getting back.

@richardh99999 11 ай бұрын

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?

@HavensConsulting 11 ай бұрын

All our PBIX files can be downloaded here :) www.havensconsulting.net/blog-files

@HavensConsulting 11 ай бұрын

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

@lakshmikanthparvathapuram1250 4 жыл бұрын

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

@PandemicLightroom 4 жыл бұрын

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?

@HavensConsulting 4 жыл бұрын

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.

@akhilreddy8220 4 жыл бұрын

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

@HavensConsulting 2 жыл бұрын

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

@sky3gpp 3 жыл бұрын

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.

@HavensConsulting 3 жыл бұрын

Apply an ALL filter against the time intelligence table and the calculation will work with same period last year 🙂

@sky3gpp 3 жыл бұрын

@@HavensConsulting thanks. It is working now.

@HavensConsulting 3 жыл бұрын

@@sky3gpp Excellent!

@macrocosmique 4 жыл бұрын

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

@damionc 2 жыл бұрын

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.

@HavensConsulting 2 жыл бұрын

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!

@damionc 2 жыл бұрын

@@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_bayes 4 жыл бұрын

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.

@denxx6126 4 жыл бұрын

Thx... How did you create „Date Range Card? Custom Visual?

@HavensConsulting 4 жыл бұрын

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

@andrevioti 4 жыл бұрын

@@HavensConsulting Could you please show how are you calculating the "Average Admission Rate"?

@mohamedaboobackersiddique7322 4 жыл бұрын

hi reid, will this technique can be applied to custom 445 calendar ?

@HavensConsulting 4 жыл бұрын

Hi Mohamed, for any custom periods you would need to set that in the ranges for the DAX generated table.

@andrevioti 4 жыл бұрын

Havens Consulting Could you please show how are you calculating the "Average Admission Rate"?

@HavensConsulting 4 жыл бұрын

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.

@gavinlin56 4 жыл бұрын

Great stuff! But why my sorting doesn't work, it is alphabeti now?

@tantinytanjung9769 3 жыл бұрын

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

@HavensConsulting 3 жыл бұрын

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?

@muhammadusman7272 4 жыл бұрын

If we need Last 3 Months data, then what is Dax query. Like we just need Sep Oct and Nov data. Please help

@HavensConsulting 4 жыл бұрын

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?