Thing now actually before we get started what i'm going to say my friends is that after years of teaching power bi i realized that there was a a step missing and that's why people.
Were struggling often with power bi and that missing step is the thought process now i talk more about why that is so important.
And why that is often overlooked in the video that i'm going to connect either in the corner here or down below in the description and if.
You're on the mobile make sure to tap on the description to watch that video so watch that video after you have gone through this video but just to let you know.
To solve novel power bi problems so again thought process is the missing step if you've been struggling with power bi keep getting stuck.
Can't solve real world problems then that's the one that you need to gain so i'm going to break it down for you so let's understand the scenario that greg is working with.
And i'm telling you calendar is one of my favorite things in power bi because it never fails to surprise me i i thought i've seen it all but now greg st gave me something new.
Which is a company who has three fiscal seasons and again i never quite seen that you know but uh so let's look at here let's what's going on so i was used to this the idea of a.
Fiscal year so i worked at microsoft and their fiscal year starts in july but they had four quarters like normal people and but this company has three fiscal seasons it starts in.
Posts Related:
- Microsoft Power Automate overview
- Slide Transitions in Powerpoint - "Morph Transition"- **Free Download**
- Carteirinha de Membro Grátis e 100% Personalizada Word
June so june july august september - ID Card Make
That's one fiscal season so right so fy 20 and if you're thinking about calendar year so i think fiscal year 20 actually.Starts in calendar year 2019 and again i i kind of understood that for fiscal years but but the season was really interesting june july august september.
October november december january february march april may and greg's question was that how do i calculate the season to date so let's step back for a second and talk.
About calendar table so start with starting with the gregorian calendar and that's the classic calendar january through december.
You know everybody knows that one right so and then the next one is what i call the fiscal calendar now this is again kind of like microsoft's where the only thing that's different is.
That the start of the year is shifted so in case of microsoft the start of the year was july 1st that's a fiscal calendar and then we.
Have what i call the custom calendar now these are really different and exotic and here i've pulled up an example from the national retail federation these are often used in.
Retail and if you notice here so the key way to differentiate between a custom calendar is when a month is no longer a month.
So if you go to why microsoft walk into microsoft and uh or and some other company which uses a gregorian calendar and you would ask what is june they.
Would all agree june is june 1 to june
30th now custom calendar does may or may not agree right so custom calendar that's what it makes it unique.And as you can see in some of these examples like it's called march but it has april dates and and again vice versa like it's called march here but as you.
Can see this has february 28th in there so the rules don't apply now first thing to realize is that that this calendar is really a fiscal.
Calendar right so it's not a custom calendar right because again a month is a month it's a month right so they always agree in month so that's a good understanding so let's.
Before we get to season today so since they agree on the definition of month actually you know what i'm going to slow myself way down so again guys this is.
The thought process part which you need to understand right so how am i thinking i'm trying to figure out what the heck kind of calendar are they using right so um and why are we even talking about.
Calendars well because of the awesome time intelligence functions built into power bi and here are the rules or well here's.
How it works so power bi diamond intelligence functions can handle gregorian calendar so it has a whole.
Bunch of functions dates mtd quarter date year-to-date and a whole lot more right previous year all of that stuff is there and they can handle this and they.
Also have well the same functions take an optional parameter where you can adjust the start of the year right so they can also handle this so power bi is awesome there.
But things totally break down in a custom calendar now why i love the scenario that greg presented that it's kind of a cross between uh fiscal calendar and custom.
Calendar and i'll tell you why so fiscal calendar microsoft can handle if you say oh my year starts on july 1 and it can give you month to date other things like that so that's where.
We're gonna start so since we understand that it's a fiscal calendar which means a month is a month is a month month to date greg doesn't have to do.
Anything so here's a month to date formula and you can see it is working and i'm going to show you the pattern and of course it's really really easy that's the.
Awesomeness of power bi so uh hold on yep there we go so sales mtd and guys if i forget to mention you can get the file at learnpowerbi.com download so here.
Again such a simple pattern that's what i love about power bi just say calculate this and use the power of time intelligence functions dates entity and it works.
For physical calendars and gregorian calendars does not work for custom calendars we talked about that so this works and you can see it kind of.
You know just kind of adds up for the whole month and if we were to uh change a filter really quick uh there we go is uh let's try.
Uh hold on one sec all right so if you do multiple months which is what i just did you can see what the monthly date measure is doing right it kind of adds up for the month.
And it resets adds up for the month and resets right so so again for fiscal calendar which is what it is the the time intelligence functions do.
Work generally right but would they break down because they don't have uh the concept of seasons i mean if you had.
Quarters i think that would have worked but seasons they don't have a concept of that so the first thing that we need to do is to build it into our calendar table so i'm not going.
To go too much into that now of course we cover the calendar table quite extensively in in our video online and again we're going to link it in the.
Corner here or that corner i don't know one of these corners and of course down in the description we'll put a link to our uh complete series and of course.
Those who are members in the learned power program have access to that as well so so all right so we need to build that into a calendar table.
One quick note that i'm going to say here is that you would build the fiscal calendar in the same calendar you don't need two different calendar tables for.
You know one has the gregorian calendar maybe if you use that in some cases and then have a physical calendar the same table is going to have both the values there so this.
Is our ultimate calendar pattern of course again the link is the same one that i shared earlier which shows you how uh how to use this and here we have made some changes here.
To just uh add the fiscal season so let's see if i can show that yep there we go so you can see uh and you know if you remember.
