Create slicer with Custom Time Periods in PowerBI | Time Intelligence | MiTutorials

hello everyone and welcome back to my channel Mi tutorials in this tutorial I'm going to teach you how to create a custom slicer with some time intelligence function which lets you filter data based on your selection for example in this drop down over here I have month to date I have quarter to date I have last 10 days last 15 days and then also as custom selection for example if I select month to date today is December 26th and I am going to see the data all the way from December 1st up till today likewise if I do quarter to date I will see the last quarter data which is from October to December and then there is last 10 days you will see the data from December 16th onwards till today and then there's also a custom option over here which will let you choose the duration that you want the start date and end date so this is what we will be learning today so let's get started with this tutorials first of all let's take a look at the data set that I have over here I have a simple sales table over here which contains the order date and the sales amount this is these are the two columns that we will be using in this particular example and then I added a line chart over here with the order date on the x-axis and on the y-axis I have the sum of sales amount so before we start writing the Dax statements over here we need to add a couple of tables to our report so let's get started on the Home tab let's click on enter data so this table is going to be the slicer values for us so let's and call this as periods and then let's start assigning names to it first is we want month to date and then the next is we want quarter to date and then I need last 10 days last 15 days and then I also need a custom date selection and then let's add another column over here and call this as order buy so that I I want to give a sequence of numbers to these rows over here so that I can just refer to those numbers instead of referring to these string values over here I'm gonna just say one two three four and five and then I'm gonna call this table as slicer and click on load so once this particular table is loaded what I can do is I can add a slicer and bring in the PTO column that we just created into this particular slicer and you can see that we have now a slicer which we can select and choose the slicer that we want to look at however this is still disconnected and it is not filtering the data as of now but we will have to work towards that so now we need to add one more table to this particular report so I'm going to go here to the modeling tab click on new table and call this table as dim underscore dates is equals to I need the distinct date from my order table so this is the simple Dax here to create a new table with all the distinct dates that are available in my sales table so now we need to write a DAC so that power bi understands so when I click on month to date power bi needs to understand that I am looking at the data starting from December 1st because today's date is December 26th power bi needs to understand that I am looking at the data from December 1st to December 26th likewise if I do quarter to date it needs to know that I am I am intending to look at the data from October 1st to December 31st right so how do we do that so power bi has an inbuilt function over here let's I I'm gonna just create a measure over here to explain you how this one works I'm gonna just call this as month to date is equals to I'm gonna type in the function over here called as Max of the order date from my sales table and then I'm gonna say in look at this particular uh value or the order date and then use the dates MTD function so this basically returns a set of dates in the month up to current date okay here I'm gonna pass in the date that we've created from the dim date table and then I'm gonna post the bracket here and press enter let me just add the order date from my sales table and change this to order date instead of hierarchy let me just align this and what happens when I bring in this month to date measure over here wherever it is not December it returns false and if I scroll down here to the month of December you will see all of the December month it is showing as true now if I wrap this up with another function as int and close this bracket and press enter this will now turn to zero and one a binary value wherein 0 is false and one is true okay so this is what basically the function is doing over here so now let's get back uh let's create a new measure and I'm going to call this as period selection so this is the measure wherein all the magic happens related to this particular slicer and then this particular visual gets filtered based on that so let's begin with um defining some variables over here I'm going to Define a variable called as period selected is equals to I'm going to use the selected value function selected value and then I'm gonna use the order by column over here so basically order by will return so whatever we've selected for example if I select MTD over here it's going to return the value as 1 because 1 is assigned to MTD if I select quarter to date over here it's going to return 2 as the value okay so now let's go to the next line over here and add a new variable I'm going to call this as month to date is equal to so this is where we will identify which month we are currently in and which are the rows that needs to be identified as month to date so I'm gonna use the same dags that we used over here so I'm going to type in Max the order date from the sales table I'm gonna look that up in the uh dates to dates MTD table over here and pass in the dim date order date and close the bracket okay let's go to the next line and repeat the same thing but instead of month this time it's going to be quarter to date I'm gonna just change this and to quarter to date and over here instead of MTD I'm gonna replace this with qtd okay all right so now we have achieved two of them now let's look at how do we calculate the last 10 days or last 15 days sales because there is no inbuilt function in bi to do that so let's define another variable here and call this as last 10 days is equals to so max of order date I want to look that up in my table that I'm gonna type over here so I'm gonna filter the table over here the filter is now going to be on the dim date table comma my date from the order date table is less than or equals to today and the order date is greater than equals to today minus 10. so basically what this function is doing is it is filtering the dim date table and it is looking for the order date column and it is filtering for anything that is less than or equals to today and it's also looking at the same order date column and then filtering for anything that is today minus 10 days so today is 26 and it goes and looks at the last 10 days okay so I'm gonna close the bracket over here I've created a copy of this particular variable statement over here I'm gonna replace this with 15 days and then change the value over here to 15 as well and now I have completed uh the month to date quarter to date last 10 days last 15 days we need one more variable statement over here which will basically help us use the custom selection over here so what I'm going to do is create another variable and call this as custom I'm gonna say Max of odd order date Max of order date in all of the dim date order date simple enough it is now looking at every Row in all the dates that are available in this particular table okay and now let's type in the return statement so return I'm going to use the switch statement if my selected period period selected here is one right remember that it is returning the order by values over here so if my period selected is 1 then it needs to be month to date so this is the variable that we have created over here comma I'm gonna go here to the next line and type in if it is 2 then it needs to return quarter to date comma if this is 3 then it needs to return last 10 days if this is 4 comma it needs to return last 15 days comma if this is 5 then it needs to return custom selection oops I missed one comma over here I'm going to add a comma Here and Now what I'm gonna do is I am going to wrap this up remember we've wrapped this up the with the in statement here to return either one or zero so I'm gonna do just that over here and confirm this particular Dax measure over here let's close this I'm gonna just remove this particular table over here so what we need to now do is I'm gonna bring in this particular measure that we created into this particular visual selection over here and then filter this by S the one and click on apply filter so right now we've selected month to date and it is now showing us the values here from 1st of November all the way up till 25th of December so if I select quarter to date you will see values here ranging from first of October all the way till from November to December likewise if I choose last 10 days it is only giving me the values from December 2016 to December 25th and I can also do a custom thing which we will get on to right now so now we need to add another slicer over here which will let the user select the custom dates so let's add a slicer over here and bring in the order date into this particular slicer I'm gonna go here to format Visual and turn off the slider over here because I don't need that slider and I just need this bit over here and what I need to now do is basically activate this slicer only when the custom selection is made if something like last 10 days or month to date is selected I want to disable this particular slicer so how can we do that for that I'm going to add a new measure over here and call this as disable dates likes it is equals to if selected value and order by from the slicer table is equals to 5 then 1 else zero I'm gonna close the bracket over here so basically what this is doing is if I bring in this particular card over here into a card over here uh sorry if I change this to a card and if I select custom over here this turns one if I select anything else it remains zero so what I can now do is I'm gonna come over here and bring in the disabledate slicer into this particular Visual and enable this only when it is one angle and then I'm gonna click on apply and since both of them are slicers I'm going to select this particular slicer over here go to the format tab click on edit interactions and change this to filter if I select last 10 days over here this is grayed out and it does not let me make any selection over here but however when I click on custom over here this gets enabled and I can choose any month that I want over here for example if I want to look at the month of February I can simply come over here select the month of February and the data will appear here only for the month of February so I just aligned both of them over here and also if you notice that this is not in any order so what we can now do is let's go back to our table over here go to the uh slicer table that we created select the column here which is periods and go to the column tools and click on sort by column and buy order buy so once this is done you will see that they are now in order from month to date quarter to date last 10 days last 15 days and then there is custom as well so with this our custom slicer is now ready I can choose month to date quarter to date and last 15 days and also a custom which will basically enable the selection of dates over here so that's it guys in this particular tutorial I hope you enjoyed this tutorial you learned something new today please consider subscribing to my channel for more such tutorials

Channel: MITutorials

Views: 13,881

Keywords: Microsoft, MS, Excel, Spreadsheet, Help, Guide, Beginners, Basics, Tutorial, technology, Microsoft Excel (Software), Tips, Format, AutoSum, Calculations, Powerbi, Analysis

Id: Xi86HHEaY_M

Length: 14min 6sec (846 seconds)

Published: Mon Dec 26 2022