How can you create a better aging report in Power BI? Something that looks like this!
Now let's see where we are starting from. Aging report is a great report for data sets like this.
In this case we have work orders but it could be anything else anything else which has kind of a
start date and an end date or a finish date but of course some items in this case or some work orders
are still incomplete so those dates are blank but and we just need to get an idea of how old are
these things were they pretty recent or are they from really long time ago now you could do that
in a simple table like this now this is showing you the total open orders great and it's showing
you a distribution and it's showing you that most of them as you can see the big numbers are from
the recent months but there are some from uh you know who are pretty old as well but we could show
it so much more beautifully in power bi we could create a report like this which not only shows
you the high level numbers at the top here we're seeing the open orders we're seeing the average
order age in days but we're also seeing a really beautiful distribution by their age in days and if
you resume in that you can see that yep you know kind of zero more than 50 more than 100 days and
so forth so you see that beautiful distribution and of course once you have that you can do with
power bi when only power bi can do which almost looks like magic to me still which is you can dive
deeper into it so you can say hey show me what's going on with these ones which have been open
for a long time so i'm holding down the control click to select that and then maybe i'm sitting
down with the person in charge of the germany division uh maybe the sales of the manufacturing
unit and i can just hold down control and click germany again and now i know exactly which issues
i need to talk to them about right so we can say hey let's focus on serving carrie sabrina larry
they have orders from germany which are open for a long time again just an example but you can see
what's possible with power bi with a relatively simple report and frankly that's what i love
about power bi it's a simple stuff yeah there's a complex stuff but you know it scares me just like
probably scares you but i love that so much is possible with something really simple so let's
see how we can do this now if you are a power bi beginner or still feel like a beginner then
this channel is for you my friend so make sure to subscribe and click that bell so you are notified
whenever i answer your power bi questions now this question comes from our learn power bi member
paolo damiani and paolo and i discussed this issue where he was working on for his client and
if you are interested in asking questions well all you have to do is go to learnpowerbi.com
question so let's start building the solution but before that let's start where i always like to
start which is in the model so model is the heart of power bi trust me this should take at least
80 percent of your focus not the visualization visualization comes easy if you have the model
right now in this case our model is pretty clean it's all set up and we're really focused on the
work order table now the both the data table and the lookup tables are important but uh in
our case for solving this purpose pretty much we can focus on the work order table itself
by the way if you have no clue what i'm talking about data table lookup table then check out
my power bi tutorial which now has i think more than four million views big thank you my friends
all right so let's uh so now that we have seen the data uh let's look at the work order data itself
now this is shown in power bi just to make it easier for me to explain it to you i put it in
excel as well just as dummy data so imagine we have the work order number which uniquely
identifies a work order or a specific item and then we have some kind of a start date and then we
have complete date now of course we're focused on these items which have the complete date as blank
and think about it then how are you going to calculate the age of these items now we could do
is i could say oh well i'm just going to take today's date and minus the start date
and that'll give me the age but of course you notice that this isn't quite right and you can
see that these items are actually not open so they should not be counted so of course we're going to
have to do something a little more sophisticated and we're going to do so we're going to say if
this is blank and i think that's how you do blank in excel
i feel more comfortable in power bi now than that right so if it is blank then
it is open then let's calculate the age otherwise just leave it right so again
sometimes i do this in excel and i call this the human learning i always say that human learning
comes before machine learning so before i can teach power bi something i gotta learn it myself
and i often break it down and step by step now the odd thing is that that's what you need to learn
and master power bi people think that oh i'm not smart enough actually i think at least
that's what works for me i need to dump it down i need to break it down like small simple steps
because that's how power bi works as well so yeah you need to dump yourself down all right i'm
getting carried away so let's come back here and of course we're now where are we going to do this
in is in the query editor now of course some of you may be thinking or asking yourself right now
hey shouldn't we create a measure for this you probably should but this is the easier approach
and since this video is at least focused on the beginners i'm going to show that to you at the end
we can talk about the measure approach in a bit all right so here we are in the work order table
and we have the order start date and order complete date so to add a column we're just
going to go to add column over here and then i'm just going to click custom column
all right there we go let me zoom in so for custom column uh i'm going to call it age
and here so i need today's date which is date dot from date time dot local now
and that is today's date and of course i can do minus
uh hold on guys okay and i'm gonna find that column and i just double click on that oh it was
right there so minus start date but again remember we had a test in excel we had to check that you
know we wanted the completed date to be blank so i'm going to add the same check here so let's go
back in again and i'm going to say if oops if order complete date is null not mall null then
uh then all right then we're returning this answer otherwise we're just going to return the blank
all right looks good no errors i'm going to hit ok
and yup uh well we'll know in a second i see a whole bunch of nouns but i'm pretty
sure the data is there so i'm going to close and apply and i'll be right back
and by the way if you are following along then make sure you have the file from learnpowerbi.com
download all right so now we have in the fields the work order age so let's put that on here
and i usually like looking at things oh did that work no okay let me try that again drag and drop
and i often like looking at things in a table great sometimes by default you drag something
and it shows a different measure and i already know that there is a problem uh
because i did this when i was practicing this as well which is you notice that the sorting is wrong
and i forgot to change this to a number so let's go back to our query editor and i'm going to
change this to a number so work order go back and age i can see from this icon over here actually
a lot of other fields need to be changed but we can ignore those we're focused on this so we're
we're going to switch this to a whole number age looks good now but of course we're looking for
the the count of items by age so i'm going to add the order count by the way that's a really simple
formula count rows and now here's the thing here now we can see the order count by age but there
are two things going on for one we probably um you know so so the ones which have been closed
are showing up in this bucket now we can leave it as that there are lots of ways to filter
we can say that age is not blank we can say that order complete date has to be blank
but since we're doing this in the query editor i'm just going to go back and add a cleaner column
which will be the work status so work order and i'll just go back here add column custom column
and i'll say work order status and it's going to be really simple
similar to the other one and it'll say if the order complete date is null then it is open
else it's um else it's closed does that look good oh the equal sign
okay that looks good work order status open and closed and let's try again
so now i have a little bit of a cleaner column that i can use work order status and i can add
this to the page or i can add this to a specific filter in this case i'm just
going to add it to the page and i'm going to say i only want to see the open items
and now that one is filtered out now of course we can
visualize it just like that so again when i'm working with measures or things like
that i mean this one is pretty simple but i still like to work in table or matrix
and only then do i think about visualizing it so in this case let's just try a column chart
so you can see that we're not quite there yet i mean yes it is showing order count by age
but that's not how we want to see it right we want to bin it or group these
so we can have a cleaner view now that's thankfully really easy to do in power bi
for that we're going to go to the age column that we just added click on the three dots here and
we're going to create a new group that brings up this dialog box and in this case we're just going
to leave it to default bin you can explore the other options if you like and i'm going to hit ok
and that is going to give you another column here so let me leave this one as is i'm going to make
a copy and the second graph we're gonna uh use the bins and you're gonna see the difference so
instead of age i'm gonna drag and drop the age bins over here and you can see how this looks
a whole lot cleaner right so you have uh you know kind of these different bins is it one zero fifty
hundred one fifty i mean those are the labels but uh you know the bins have uh let's see if you
hover over that it's uh yeah it's kind of an odd number but but still you you kind of get an idea
of the age let's talk about what are the possible next steps you could do from here all right so i
cleaned up the view a little bit added some basic measures and again if you grab the file you'll be
able to examine some of these other measures and one thing i realize is that the age i can actually
go and oops and edit that group and the bin size it does try to calculate it automatically based on
the different values but i realized that it's it's better to not have the bin size as this
weird number 19 so i'm going to i'm going to just change that to 20 hit okay and that'll just give
us cleaner boundaries but again we'll talk about the next steps and i'll show you what else may be
possible here so this gives you the same view that i showed earlier with all the awesomeness that you
can do you can kind of click filter and so forth actually let me fix one more thing all right so uh
you notice that the filtering was a little different than if you go back to the beginning
of the video and that is controlled by edit interactions so i'm not going to go into that but
notice that now the filtering works differently it doesn't highlight it actually filters this one
so that's great so as we did earlier you can kind of click and select and focus in on just
the germany items and again grab the file now let's talk about what else is possible i'm
not going to be able to cover this in this video however that is our plan for actually our advanced
power bi training program that we will be announcing soon you can go to learnpowerbi.com
advanced if you're interested in that and find out more but let's see some of the ideas that i
had of what else could be done i i love this data set and i think it's super interesting
the first idea that i had was to again these bins are kind of awkward and i really
don't have when i build it for my client it's usually more specific and i would probably do
end up doing something like this and notice that you know there are cleaner labels 0 to 10 10 to 30
and so forth and notice something interesting here that these bin gaps are not the same and
you know sometimes clients do want to see this way so i want to have more flexibility this one
is 10 days this one is 20 days when it's 30 30 and of course i love doing this part where you don't
want to show a long tail like there's you notice how in this in this graph there's this one guy
sitting out here and imagine there were a few of these and it's always hard to kind of select them
or something right so i just want to say you know what 90 plus and then they could click on it and
they can see the details and and so forth right so kind of these customized bins i love doing that
but then of course uh god i don't remember now if i mentioned but i i think i did where i talked
about the approach that i just showed you used columns in query editor right another approach
you can do is using measures and it would be more powerful but it is slightly uh more nuanced
but with that you can show a trend so you can show kind of how many orders were open
at any given time over time and you can see that something bad happened with this company right i
mean around may 2021 right and and not just that maybe you can show the the trend of the average
age and you can see kind of that spiked up as well but this functionality it's you can't do that
with the approach that we showed in this video but it's definitely possible in power bi well i hope
you enjoyed that video and uh i love answering your questions so if you have any questions just
go to learnpowerbi.comquestion or leave a comment below and i'll get back to you take care bye on
you