Switch Between Calendar & Fiscal Year in Power BI (Must-Know Trick!)

a lot of times the business is going to come back to you and ask you can you create a visual where I can shift between calendar year and fiscal year at my convenience in this video I'm going to talk about that how do you build such a visual where it's easy for the end user to shift between calendar year and the fiscal year and did you notice that line in the visual yes we are also going to talk about that let's start before we jump into creating this particular visual let's break down the mechanics of

this particular Visual and take a look at what what are the moving parts of this and how are we going to go about making this stuff so obviously once you click on the financial year it actually shows you the financial year if you actually click on the calendar year the data changes and it shows you the visual as per the calendar year now there are two or three moving Parts in this number one is obviously this particular table that we have which is where we have used the calendar year and switching this is going to

produce a fiscal year so that is one sort of date table that we'll have to figure it out which is where we need both the calendar year as well as the fiscal year that's part number one part number two obviously is this slicer and we'll have to figure out that how do we actually Place calendar year and fiscal year in the slicer and that should actually interact with my visual and finally we'll also take a look at a few calculations that go along with the visual so at the moment we just have total sales and

byd sales but hey there could be more calculations right here I'm going to give you a few examples and that should help you build your own calculations further before we start putting together that calendar or the date table it's important to take a look at the data model first so we have a very simple sales table right here linked with the products table with nothing but the product code and in order for me to present the data in any sort of calendarized fashion I need to have the date table and the date table should contain

both the calendar year as well as the fiscal year let's just start with that all right I'm going to go over to the table tools and make a new table and that is going to give me the ability to make a calendar table right here you can give it whatever name I have given that as calendar now we'll start writing some code and I'm going to start with a very simple calendar Auto function so I'm going to call this as VAR my table and then I'll say hey I want to maybe use use the calendar

Auto function now there are two functions in Dax you can also use that using power query but for now I'm using Dax and there are two functions in Dax that you can use either a calendar function or a calendar Auto function I've discussed both of these functions in another video in case you want to take a look please take a look but the advantage of using calendar Auto function is that it's able to detect the date automatically from the data model so hence I'm using calendar Auto function so let's just say that I'm using VAR

my table CER Auto and I'll just return that particular variable Which is my table at the moment if I commit on this I press enter you're going to see that I get a calendar table automatically created or a date table automatically created which is where the first date is 1st of January 2011 and all the way up until December of a certain year now obviously since I want this to be adjusted to financial year as well what I'm going to do is I'm going to adjust this to Indian Financial year and you can adjust this

to any other Financial year in India the financial year ends in March so I'm going to adjust this to March I'm going to create a variable called year and month and I'm going to give that number as a three why three because in India the financial year ends in March and hence we have number three now I'm going to use this particular three in my calendar Auto function so I'm going to move that up and move this down and this is where I can use that hey please create the calendar table starting from April onwards

so I'm going to say hey this is where I'm going to say year and month and if I now press enter you going to see that the calendar table actually starts in April of any particular year next up we'll create a bunch of columns that belong to the calendar year and not the fiscal year I've already written the code I'm sure you can find the code at plenty of places you can also download this particular file but I'm just kind of moving swiftly by just creating the logic quickly so what I have done is I

have used this particular calendar table right here and I'm creating four columns by the calendar year which is the sort column for sorting the months then we have the month column the quarter column and the year month column now at the moment I'm returning my table which is this particular variable but I would rather return this particular variable let's just take a look at what happens if I do that so date table press enter and we sure enough get four columns added right here to neaten things up what we we can also do is we

can just kind of give this a label this is my calendar columns and now let's just move on to create similar columns for fiscal year as well I'm going to start off with the label this is my fiscal year columns and I'm going to paste the code right here which actually belongs to the financial year now you can obviously take a look at the code but what the code is essentially doing is creating four columns which is financial month the financial quarter sorting order and the year and the month column so if I just actually

commit on this you're going to see that we have four more columns created if I just zoom out a bit you can see that we have four more columns created which is the month the quarter the sort order and the year month once our columns are created it's important that you link the calendar table with your sales day table so that the relationships start to propagate the filter so I'm going to take the date table right here the date column right here and Link that with the date column in the calendar table it asks that

you want to build a one to many relationship ship sure enough that's exactly what I want to do and that's the relationship created okay quick interruption in the video if you're enjoying the video so far you're going to absolutely love my courses on powerbi tax modeling M language these are hard courses more advanced courses which is where I teach the users how to think about the problem lay out the logic first understand the principles of solving the problem in powerbi and then start building the solutions of your own once you understand how the underlying principle

or fundamental works you'll then be able to take the same concept and even apply that to your own problems in parbi hundreds of students have joined the courses and they have benefited a lot I'm going to leave a link to the course in the description of the video and you should definitely take a look at that before we move any further let's just test out the work that we have done that is it working in the first place or not so what I'm going to do is I'm going to actually go ahead and create a

matrix visualization that's nothing but a pivot table and in that I'm going to use the column that we have created which is somewhere right here so in the calendar table let's just first go ahead and use the calendar year so we did this year month right here so I'm just going to drag that right here we have year month and against that I'd like to present my total sales and I can just do that now at the moment you're going to see that the ear is not pretty much sorted and that's the reason why have

we created the sorted column so let's just go back to the calendar table in the calendar table I'm saying that this particular column is going to be sorted in this particular order we've already created the sort order for that I'm going to select on that particular column go over to my sorting right here and I'm going to call this hey why don't you get sorted by this column right here if I just go back and take a look at the visual sure enough it sorts in the correct order all the way from January up until

the last month where the data exists I'm going to quickly copy this particular Visual and on the right hand side I'm going to create a fiscal year visual so I'm just going to go ahead and say hey I am looking for the financial year month that's my financial year month I'm going to drag that and this again is not sorted in the proper order I'm going to go back to my visualization and hey say that this particular thing is going to be sorted in this particular order we have already done that I'm going to click

right here again go to the sort drop down and say that this is going to be sorted in the financial year sort order let's just take a look yes the results are fine so we have Jan Feb and March which is correct and then the year changes and this shows the next year April 12 all the way up until March 12 and then April 13 so on and so forth so at the moment everything seems to be working fine but these are two different visualizations and I would want to kind of build like a toggle

or a switch or a slicer which is where I I can pick up what year do I want do I want to see Financial year or do I want to see calendar year and depending upon what I choose either this is going to be shown up or this is going to be shown up that's what my purpose is and this brings us to second part which is where we start creating that slicer next up is the slicer and the technique that we are going to use is nothing but Fields parameter I'm sure you have worked

with that in case you're not this is absolutely brilliant please take a look so I'm going to go over to the insert Tab and that's where I can find something called as Fields parameter not in the insert tab sorry about that but in the modeling tab we have new parameter and that's that's where we have the fields parameter now in the fields parameter what I'm going to do is I'm going to give my parameter a name which is nothing but ear selector and in the ear selector I would like to drop in the fields by

Fields I mean columns and we have two columns so if I just open up my calendar table I'd like to switch between the year month which is my regular calendar and then I'd like to switch between F year month which is nothing but my fiscal calendar do you want to add a slicer on the page sure enough I would like to do that click on Create and that's where I have the slicer added now it's not really in the way that I would want it but it actually gives me a good working prototype so at

the moment the problem is that clicking on the slicer does nothing so I would want something to happen in my visual so let's just delete one of the visuals let's just continue to work with one of the visuals and I'm going to say that hey based on your selection I would want this pivot table to take effect and how do I do that I already have created this ear selector and this ear selector column I'm going to move that in the visual so I'm going to go ahead and drag that in the visual itself and

I'm going to go ahead and ear selector right now is coming in columns but I don't want it in columns I rather want it in rows so I'm going to move that from columns to rows and delete the existing one so delete that and right now it actually shows me some promising results so if you take a look at the visualization at the moment it shows me hey we have Jan Feb March good enough and after that we have April all the way up until March which is right here and then the year changes once

again if I change that to Let's say a year month this actually shows me all the way from January all the way up until December and this is working just fine at this state the year month and the financial year month they working but the end user would not get to know that what is the meaning of year month and F year month unless we explain that so I would want the representation to be crystal clear and I would want to write calendar year Cy and financial year FY well the way to do that is

that make a slight change in our table created so I'm going to go over to the table that we have created which is the ear selector table this particular table at the moment we have three columns this particular column that is shown up in the slicer the actual fields that are being moved as we click on the slicer and the order this is order number one this is order number two now we going to create another column in here and I'm going to say hey this is going to be my calendar year and this is

going to be my financial year I'm going to do that I'm going to close the bracket and this is going to create one more column if I just maybe can do that correctly so once we we have this column created I'm going to call this column as my year and this is where we have calendar year and fiscal year and now this is going to be a much clearer representation of what we are going to show in the slicer so I'm going to go ahead and rather than actually picking up the First Column I'm going

to pick up this year column and place that in the slicer itself so I'm going to go here and delete the year selector and just place the year let's just see if that is working at the moment or not so if I click on year sure enough if I click on financial year sure enough both are working just fine the Aesthetics and the visual representation is not only the reason why we have created the calendar year and the fiscal year there is also a technical reason which you will just understand in just a bit but

for now let's just go ahead and proceed by adding one more calculation to our visual which is nothing but yd sales all right let's just start writing that yd sales measure I've already created a blank measure let's just take a look at how do we write that so I'm going to go ahead with with a buil-in function that is called nothing but the total vty function at the onset it expects you hey what is the expression or the calculation for which you would want to find the YTD I'm going to say that hey this is

nothing but my total sales calculation then it asks you hey do you have a calendar table or a date column in the calendar table sure enough we have that and I'm going to maybe lead my calendar table and my date column in there and then I'm just going to close the brackets and press enter this is going to give me my calendar year yd that means all the way from January up until December let's just take a look at the results for this so I'm going to drag this calculation onto my visual and you're going

to see that the results if I just maybe change that to the calendar year you're going to see that the results are correct uh so here we have about $1,200 and this actually keeps on accumulating the results till the time we reach December and then after that the results are refreshed that means the total accumulation starts all over again from December all the way up until the end of data but if I actually change this back to financial year you're going to see that the results are not quite correct we would actually want to start

with April so this is where we would like to start and this is where the total should reset to the month of April and then it should start accumulating all the way up until March this is the place where it should accumulate until and then it should restart again from April that's the way that I would want so how can we change this Behavior there happens to be one input parameter through which we can change that but the change will only come in case you switch between the calendar year and the fiscal year so here

the change will come due as per Financial year and here the change is not going to come due as per calendar year so we'll have to create two versions of the calculations and we'll have to take in account that what is selected in the SL and then replace that in the visual so I'm going to go over to my YTD sales right here now in this I'm going to first of all take a look at what is selected in my slicer right here and to do that I'm going to create a simple variable call this

as VAR and I'm going to say ear selected and I'm going to go ahead and choose the selected value column of the ear which is there in this particular table now just a while ago if you remember I told you that this is something that we are not doing it for aesthetic purpose purposes there is another meaning to that or technical meaning to that now whenever you have the original column being used in a measure it doesn't allow for that and you have to use some surrogate column some additional column that you have created and

that's the also the reason why we created this column so that we are able to pick up the value in the slicer itself well I could have created the error but I'm just letting you know right now nevertheless let's proceed so this is actually going to give us the value that what is being selected in the Slicer in case nothing is being selected in the slicer we would want to just have the calendar year so I'm going to provide an alternative value which is nothing but my Cy that is going to be the value and

now I'm going to say hey I would want to kind of do another VAR and do my let's say calculation this is one of my calculation and there is going to be another calculation which is going to be as per my financial year so I'm going to write a very simple switch statement so I'm going to say switch and I'm going to say hey switch true and check when the condition becomes true and what is my condition why don't you take a look at the year selected if the year selected is equals to calendar year

then in that case I would want you to happily do this particular calculation which is working as per calendar year at the moment but in case the year selected is as per Financial year so I'm going to write hey the year selected is equals to FY then I would want you to do another calculation and for which I'm going to tell you the code right now so let's just copy this code because it's pretty much the same and I'm going to say that hey I still want you to do the YTD I still want you

to consider the calendar date table but the year ending is going to be done when it's March 31st that's how you kind of finish the year and the totals are going to be reset and that's pretty much it so I'm just going to maybe neaten up my formula do the return statement and then I'm going to use my Cal do that press enter this should give me the right answer so let's just go take a look so if I just go take a look at the financial year you can see that the total is resetting

in the month of April and then going all the way up until the month of March and then it resets once again right here so on and so forth if I just maybe switch over to calendar year it goes all the way up until December from from January right here and then it resets the total again in the month of January so this is working just all right before we actually close on the calculations there is one additional thing that I would want to give it to you which is nothing but the spillover effect and

how do you turn it off what do I mean by that if you take a look at the visual the calculations are spilling over to the months where we don't even have the sales because the calendar table contains the data all the way till the end of the calendar which is end of December it actually does the calculation where the sales doesn't even exist and how can we stop that the simplest way to stop that is by creating a check column in your calendar table let me show you how so I'm going to go over

to my calendar table in my calendar table I'm going to make a simple check that is the sales present on this particular date or not that means hey take a look at this 1st July 202010 is the sales present write a true or write a false and that true and false is going to be my filtering mechanism so I'm going to go ahead and start to write a very simple code right here so I'm going to maybe create another leg of calculation which is my sales check and the logic is very very simple the logic

is something like this why don't you figure out the maximum date in my sales table so sales table date column figure out what is the maximum date once you figured out the maximum sales date why don't you take it to the end of the month so I'm going to say hey EO month well actually we'll have to give the column name first and that's the reason why we this is actually giving us the red line so let's just call this as is sales present and then I can write my formula that what is going to

be the formula for that column so I'm saying that hey find the max maximum date in the sales table date column and then why don't you take that to the end of the month so whatever is the date which is the largest please take it to the end of the current month so let's just say if August is the largest date whatever date is it in the month of August take it to the end of month which is 31st of August now I'm going to say hey why don't you take the calendar table actually the

current date column which is nothing but this date column and see that if the current date is smaller or equal to that or not so smaller or equal to that or not and that's pretty much my check if I actually press enter you can see that we have a true and false column and this is going to give you a bunch of trues and falses that means the sales existed or the sales did not exist now we're going to use this column as a filter in our formula so I'm going to go back to my

YTD calculation and I'm going to say that hey this is good this is giving me the right answer but I would want to wrap this around in the calculate function to apply one additional filter so I'll say hey calculate this very calculation I'm good with that but I would want you to apply a filter which is nothing but is sales present the column that that I have just created and that's it press enter if I now take a look at my output you can see that this in the financial year it actually stops the calculation

right here but in the calendar year it doesn't stop the calculation right here so for that we'll have to do that for the calendar year as well so I'm just going to come right here I'm going to say hey this is my calculate and do that and then this is going to be is sales present and I can just maybe close the bracket and press enter and then if I just maybe commit on this you can see that now the calculation is going to stop either for calendar year or for fiscal year the calculation stops

where the data ends at this stage we are standing on the finished solution that works fine there is no problem with it we've had a big meal but there is something missing and that something is the dessert that means that our visual doesn't look glossy and slick and sophisticated let's just do that 3 2 1 let's go and you can see that I formatted the visual and it looks something like this now there are a bunch of things that I have done which I will talk you through in just a moment those are all clicky

things not very hard to understand and I will just take you through those things now first of all I have formatted this particular table and I've removed the grid lines and everything and I have just also added the padding which is space between every single Row the headlines have been colored differently the slicer that was placed right here have been smartly brought on top of the table itself and now if you actually click on any of the values in the slicer this is actually filtering the visualization I've also added data bars here along with total

sales and you can see that it just shows you that which month has got the maximum sales that you can just take a look at visibly right here one additional thing that I have done is that I've also added this little dotted line right here that actually starts with the start of the fiscal year and ends and the end of the fiscal year why that because once you're taking a look at this particular visualization I would want to make it very apparent for the end user that you're not taking a look at the regular calendar

year you're taking a look at the fisal year which is starting somewhere and ending somewhere now at the moment this year is starting right here and ending right here now this this is going to alternatively highlight every single year at the moment we have April and this is highlighted the next year is going to be not highlighted and the next year that comes in the pivot table is going to be highlighted so if you have a bunch of years like four or five years this is going to give you a nice cool effect of highlighting

one set of here then not highlighting and then highlighting and not highlighting well the question is how exactly did I do that now that's one technique and you can actually use this technique to do several other kinds of formatting but here is how I did that now if you take a look at the highlighter measure that I have created which is actually creating this little bar effect I'll tell you how the bar effect was made but take a look at the measure first what this measure is essentially doing is that it's actually taking a look

at this particular date whatever date is it and it's finding out that is that an even year or an odd year so 20202 is even and then hence it kinds of results in a true and therefore it highlights it if I actually change this logic to is odd you're going to see that every odd year is going to be highlighted which is nothing but April 13 and April like all the way before that so if you take a look all the way this is highlighted and this is this is also highlighted so howsoever you would

want to design your logic you can design it but at the moment according to my pivot table the even logic works better once you have created that measure I want to use that measure as conditional formatting so I'm going to go ahead and show you what I have done so if I actually take that highlighted measure and I put that in my pivot table right here you're going to see that this actually creates this little column right here now this column is squeezed down to a certain length and it doesn't kind of show very big

highlighting color so if you actually open this up this is nothing but my highlighted measure that you can see it right here and this actually is producing nothing but two different colors so it's producing a gray color or no color that's what this is doing and once you actually do that you can actually use conditional formatting to highlight this particular column well how do you do that it's very very simple you actually go over to this particular visualization then you go over to the format in the format you're going to go over over to cell

elements and pick up the highlighter measure and then attach the background color so if I just maybe click on the FX right here you going to see that we have used this particular measure that's one and in case you take a look at the font color that is also this same color right here and that's pretty much it and once you actually reduce the width of this particular column all the way to almost zero this shows up as a nice touch on the visualization in case you'd like to take a look at how do you

calculate growth from one particular year to another another particular year using this Nifty visual here is another video for you to watch I'll see you in that one cheers [Applause] [Music] [Applause] [Music] [Applause] [Music] [Applause] [Music] [Applause] [Music]