And you can see in this graph that the headcount was just increasing over a month. So again, when we're looking at a month level account is correct, but when we're not looking at a month, It all goes to hell. So let's see. So in here 2020,.
It's actually adding up and we can see that even in the most recent months, the largest headcount we've had is 10,944. So clearly this is wrong. 22,000. It's not right. This is not right. This is not right. None of these numbers are right. And um, over here on this side by department, that is wrong as well..
I mean, look at that, engineering does not have 47,000 people. Now. Again, if we select a specific month, then the numbers are right, then the department breakdown is right. But what about when we're trying to look at it by year or by quarter? It's all wrong. All right..
So that's the discussion we had with Kerry thomas and I'm gonna show you in this video how to tackle this. Now, of course, if you're new to the channel and you're a power bi a beginner or still feel like a beginner,.
Thanks to Kerry thomas for bringing this to our attention. Had a great discussion with her. You can also connect with her. I'm gonna put her linkedin profile, your l in the description below. You can connect or follow her there. Uh And if you have your own questions, well then you can just go to learn.
FBI dot com slash question and send us your questions. Alright? So let's go back and hopefully you understand the scenario here. So let me take a step back and look at the data center. Right? So, if you look at the headcount data, this is what we're seeing..
So it has the department and it has for every department it says month, you know, so october november, you know, january. Yeah, It's not sorted, right? But you get the idea right? So every month it reports the number.
Now, of course department is connected to this hierarchy, which we also have. Uh So department can have a leader, entity, division, something like that. Right? So you can slice and dice in any which way you want. Uh we're not that concerned about that table, we're mostly focused on this table. So again, let's take another look..
So here we have we have the department and you can see there are different departments here and months, of course different months here. Right? So oops didn't want to do that. Okay, so let's go back and see how we can solve this..
Posts Related:
Actually before we go any further, make sure to download this file by going to learn
Dot com. You'll get a complete download treasure chest of all the files for all of our videos, including this one, so you can follow along, try out different things. Alright, so we do not want this total headcount..So again, if you look if you were to look at the formula right now in the head count is just doing some of headcount. Now I'll say that some is needed, you cannot get rid of the sum and here's why, because I think this is what's called semi additive. Let me know if guys if I got that. Right?.
So it's it is semi additive because you do want to sum it up. So let's say if you were to look at a specific month. Right? So let's say january one. Well you do want to add it up. So imagine if you were getting a total head count or remember we.
Have another look up table which groups department into leader entity division. So if you're if you're trying to um you know, kind of summarize it up, we do have to sum up these departments so the sum is there. But then again, we just need to do a test and say, hey, you know what, if there are multiple months, you know, only show me the latest..
So for that, let's create a new measure over here. We're gonna go to modeling and guys, I'm gonna take it step by step and I'm gonna make mistakes which are intentional in this video. But of course sometimes when I'm writing something new,.
I do make mistakes unintentionally as well and that's the way to go. So if that happens to you, it's perfectly okay. All right. So we're gonna go here and we're going to say, I don't want the total headcount, I want the latest head count and for this,.
Hold on. Okay. I wanted to make the fund a little larger for your latest head count and I know that I'm gonna change the filter context here. Right? So because the default filter context that's coming in is not good for me right now,.
If you need more information about filter context, if you don't understand what that means, how that works, I would strongly encourage check out my course at learn dot com slash training, but I'm gonna assume you know that. So we don't do need to change the filter context..
We're gonna say calculate that is a magic wand, that's usually the one that I used to change filter context and but the first expression is well, yeah, but before we go to the filter context, what are you actually calculating? And we still want the total head count, which is summing up the head count,.
But we only want it for the most recent month
Now or the most recent date. So I think this is what I tried when I started out, right? So I said, oh that means the calendar date needs to be the max, right? So it needs to be the max calendar date and.Uh maybe that's a little too big. All right, so you get the idea. So it's saying a calendar, give me total headcount where date is a max calendar date. And I think I need one more to close out to calculate. And that looks good for now, promising, right? And then I'm gonna go here at a comma here because I know I'm gonna need that.
And I'm going to collapse that. And now let's um let's let's actually give this guy some space and I'm gonna add my measure here. So here, and I'm gonna add the latest head count.
Now, notice that it does not work, right? So, again, it's like why didn't work? I'm telling it to do the latest date. So there are lots of ways to debug formulas one I like to do is you can use variables but for now.
I'm just going to say, well, I'm trying to use this max calendar date value, what is that? So I'm just going to I'm just gonna kind of return that for a second and I'm going to comment out the original formula..
Right? So I'm gonna comment it out and and sometimes if I'm being careful, I do I do not want to yeah, I don't want to leave an indicator for me that you know, this is not the real formula, I'm just debugging it. Okay, so let's try that a max state..
Okay, come on buddy. Okay, there we go. So here um so let's see what's going on here and hopefully the formatting doesn't bother you maybe actually change the formatting a little bit short date. Great. So.
Again, we had written the formula as oh, show me the head count where calendar date is max calendar date. But now if we look here, what is the max calendar date return? And for 2020 is returning 12 31 2020 That makes sense. Right?.
And for this one is returning 12 31 2021. So that's the filter context. And of course, as you know, how filter context works, is we're actually filtering the calendar table here. Right? And that flows downhill through these filters and again,.
If you don't get these concepts, I would encourage go through the course learn FBI dot com slash training. So this is filtering down and now if you look at the head count table you would notice that it does have.
Monthly grain of the data, how do I clear filter. Okay right so it does have a monthly grain of the data but the way we have designated the month is by using the first of the month. Right? So this is really not December one. Right? So again the date doesn't apply But in this case the data set just has December one September 1..
So of course when you send that send 1231 there's no data for that specific date. Now again, first thing I would say is that this doesn't mean that you do something hokey with your calendar table please..
My friends leave your calendar table as well as is calendar table needs to be at a at a state level grain do not create a table with a month level grain that is not a calendar table. You need a calendar table if you want to learn more..
Um we'll put a link to the ultimate calendar playlist series. It's the most important table in your model. Don't mess it up so we're not gonna mess it up. We're gonna let it be right and now of course if you come back here. So if you come back to the place where.
We were trying to answer the question right here. So let's come back and I did think about a few things I said great max calendar date is giving me the.
End of the month now there is a dax function start of month, which can be used now of course. I also thought about I can go into the query editor and actually shift the data set to for this data to have end of the month. Power query has a function there too..
Either of those would probably have been okay. But since we do have a pretty awesome calendar table, not just any calendar table, the ultimate calendar table, my friend and again we'll link to that playlist below. So what this calendar table has is I'm gonna give you a demo, it has a lot of cool fields and one of them.
Is this month, year and especially month you're numb. So knowing that our data is at a monthly grain, I can just go back to the formula and I can say you know what, I don't really want the maximum date, I just want the maximum month, year or month. Really? Right?.
But of course you don't wanna say December because December could be in 2020, 21, 2022. So we're gonna change that. And actually just to show you what that is, I'm gonna monthly or numb, I'm just gonna show you what that returns..
So when I say give me the maxim monthly or numb. There we go. So now you can see that it's now doesn't filter down to a specific date, it filters down to a specific month or 2020 december. 2020 2021 december and so forth. Right?.
So uh so this is gonna work because their data is at a monthly grain and now the specific date is not gonna matter. Alright, so let's come back here and now we can uh put it back in. So let's go to calculate.
And and I'm going to put that on a new line and we're saying not the specific date I just cared about the month, year numb. So that month you're numb.
Needs to be the maximum, right? So give me the most recent month, give me the value for that and I think I can take the debug stuff off. I'm pretty confident this is gonna work..
So hit check that box and let's take a look at it. Uh Do we have? Oh, I thought I added commas, there we go. Okay. Alright, so 2020 it's saying 1925 And I can scroll, I can actually do this, I can just click on 2020 and I can see December.
Is indeed 1 9-5. I can of course also see it in the table over here. I can click 2020 and that looks good. So yeah, this is looking good. So I'm gonna just clean up the view a little bit, take, take off the red and be right back. All right, look at us. We are looking good..
So again, for all of these graphs, I've switched out to the uh the latest head count, a new measure that we had. We have created. And you can see That it's it's all looking good. So for 2020 we had kind of checked that and you can kind of see that..
And of course here we have the year and the quarter now. Of course, if we like, we can change that to uh to kind of the uh to have it be drilled down so year quarter, maybe we can even add months in there..
So I'm gonna go here and grab a month over there. And so you can see 2020 or 2022, we can see kind of the data and you can see it's Q15823. That seems right. I can kind of validate it or 2022 10 94. Alright, so everything just works..
Of course the department works and so forth. And of course you can do any other chart. So you can have a kind of a line like this and it's dummy data, so probably not that interesting, but um but yeah, you can see for each department how they compare year over year now folks..