Solve Sales vs Delivery Date Problems in Power BI

Imagine that you have not one but two

date columns in your sales data. One is

for sales date, the other one is for

delivery date. Let me ask you a few

questions. Tell me how are you going to

solve for it. How much did you sell in

the month of January? That's easy and

you'll be able to solve it. But here's

another one. How much sales was

delivered in the month of January? Not

sold, but delivered. Okay, here's

another one. How much of what you sold

in the month of January was also

delivered in the month of January?

That's a tricky one. All right, here's

another one. The sales that were made in

the month of January, how were they

delivered in the following months? No

matter how many months there are. Now,

these kind of questions are tricky

obviously, but they can be translated to

any kind of data. Sales data where you

have multiple dates, logistics data

where you have multiple dates, supply

chain inventory data where you have

multiple dates to work with, or even HR

data when you have multiple dates to

work with, maybe against recruitment

when the recruiter is moving the

candidates across multiple stages and

every single stage has a date against

that. In this video, I'm not only going

to teach you how do you solve for these

tricky DAX questions that involve

working with multiple dates, but I'm

also going to teach you some nice

validation tricks to write DAX queries

that validate your results. You'll not

only learn about how to write good DAX,

but you'll also learn about some very

nice modeling tricks that in turn makes

your DAX smaller and smarter. You can

obviously download the files that are

there that I'm going to use and use them

in your own scenarios. Well, if you're

ready, let's start.

All right, fellas. Let's just start

taking a look at the data model and of

course answering level one question. All

right, I'm going to talk about that. So

take a look at the model first. We have

the calendar table. We have the products

table as two dimension tables and we

have the sales. Pretty standard data

model. Nothing funny going on right

here. On the visual side, I've made a

very simple visual. A slicer to slice

the year and then we have year here,

week here, and what's my sales? Now,

obviously, if you take a look at the

number, a $400,000 of sales was sold in

week 1 at 2019. Don't bother about the

comma separation here. This is the way

Indian comma separation works.

Nevertheless, if I want to know how much

sales was delivered in week one of 2019,

then how do I do that? Let's just kind

of take a step back and think about it

that how are you going to do that in

Excel? If you were working with Excel,

then we will translate that logic in

PowerBI and start solving this problem.

In Excel, I've opened up the data and if

I were to find out that how much sales

was made, not how much was delivered,

sales was made in week one. Using this

particular date column, I'm going to

create another column on the far right

and that is going to give me week

whatever week 1, week 2, whatever that

is. And then I'm going to apply filters.

Hey, keep the 2017 filter, keep the week

filter. And then I will then multiply

quantity into price for only week 1 of

2019 2019 and then I will get my sales

done in that period. Similarly, if I

have to find out that how much was

delivery done in week 1 2019, then my

filter should not be on the sales date,

which is this order date, sales date,

but it should rather be on this

particular column called the delivery

date. The filter is going to be exactly

the same like apply week 1 filter, 2019

filter, get the data, and once you have

the data filtered to those two values,

which is 2019 and week 1, you will then

do quantity into price and then you will

have your numbers ready. Now if you take

a look at my sales measure in my PowerBI

report, the sales calculation in my

PowerBI report is already doing that

unit price into the quantity calculation

for every single row of the sales table.

So this part of the logic is clear. But

the only problem is at the moment the

filter which is week 1 filter and 2019

filter is applied on the order date and

not the delivery date. So if I actually

go ahead to this particular model view

right now and I take a look at the date

column right now please note that the

two columns which are in question in the

visualization are coming from here from

the week here and then there is another

one called the year right here. So these

are the two columns which are there in

the visual these are filtering the date

and then the date is kicking off from

this particular table and then applying

the filter to the sales date right here

and not the delivery date and if these

two filters year and the week were to

come and filter my delivery date I am

going to get to see the right result.

Now what's the thing here? Should we

just go ahead and modify this particular

relationship that is currently there to

cancel it from the sales date and then

link it to the delivery date? That means

this should be the active relationship.

The answer is no. We are going to build

an inactive relationship that we are

going to use it only for delivery date

calculations. Let me show you how. So

I'm going to take the delivery date from

here and I'm going to go ahead and link

that with my date column right here. And

it says, hey, do you want to link the

delivery date with the date column? Yes,

I want to do that. And this I can't make

this relationship active because there

can only be one relationship active.

It's a one to many relationship. That's

fine. I'm going to click on okay. And

you can see that this dotted line

appears right here. And this is an

inactive relationship. That means right

now not in use. Now I'm going to go

ahead and write a measure called sales

delivered for which the DAX looks

something like this. Which is where I

will call the inactive relationship and

apply the filter to the delivery date

column instead which I saw that in

Excel. So now how do I do that? I'm just

going to say hey calculate. I am still

trying to calculate the total sales

calculation which I have seen which is

absolutely fine and I'm good with that

but the relationship is not going to be

the active relationship but instead

going to be the inactive relationship.

So use relationship is the function that

I'm going to use and the use

relationship will use the relationship

that I have created which is the

inactive sales and the delivery date.

Now note that when you're writing this

particular DAX it is necessary that the

inactive relationship exists in the

model otherwise this DAX is going to

give you an error. Now, I'm going to

take this measure and put that in my

visual. And I can see that if $400,000

of sales was done, but $524,000

of sales was delivered. Now, you might

ask me, how is that possible? I just

sold $400,000 and how could I deliver

$524,000

because this delivered sales might also

contain the sales of the last year

December's value and therefore this

number is a higher number. Anyways,

let's proceed to level two questions.

When you show this visual to your boss

out of curiosity, your boss might ask

you this next follow-up question, which

is that if the total sales in the month

was approximately $400,000, how much of

what you sold in that week, which is

week one, was also delivered in week one

of 2019? That means what part of this

sales that was made in week one was also

delivered in the same week. That's the

question that I would want to know. How

are we going to solve it? Let's just go

step back and try to solve this

logically in Excel. If you were only

having these raw tools of filters in

Excel, how are you then going to solve

it? And then we will translate that

logic into PowerBI. When you go back to

Excel, the two logical filters that you

will apply on the data are obviously,

hey, why don't I pick up week one? You

will obviously use this date column to

create a week and a year column. And on

that week and a year column, you're

going to apply a filter which is going

to be the week one filter and the 2019

filter. If these two filters are

applied, what you're going to get is

nothing but the sales made and of which

the number should tally $400,000. Then

you're going to go ahead and work with

this particular column, the delivery

date column. Using this column, you'll

again create those two columns, week and

the year using this particular column.

And then apply the same two filters,

which is give me week one and give me

the year of 2019. Having these two

filters applied, whatever number shows

up in terms of quantity into price, that

is the number that was sold in week one

and also delivered in week one based on

the two filters. Now, we'd have to do

something very similar in DAX. But let's

just take a look at how are we going to

do that in DAX. All right, I'm going to

create a measure called sold and

delivered and for which I will start

writing the calculation. Logic of the

calculation is pretty straightforward.

Please take a look at the year and the

week and this year and the week is for

the sales week and the week. this year

and the sales week should be same as the

year and the sales week for the delivery

date as well. That's it. And using these

two filters, whatever data you get, you

do your total sales calculation. Simple

as that. So, I'm going to go ahead and

start to write calculate. And I'm going

to say, hey, I'm still trying to

calculate my sales, but the two filters

that I would like to apply are the

following. So, I'm going to say, hey,

why don't you find the week number of

the sales date? And the week number

should start with Monday. And that's

what I'm writing the two for. And this

week number when concatenated with the

year of again the sales date. When you

concatenate these two the result of

these two should be equal to the week

number of the delivery date. And then

again this starts on Monday. And when

this is concatenated with the year of

the delivery date and these two should

be equal. And that's my very simple

measure. All that I'm doing is take a

look at week number and the year when

concatenated and the week number and the

year when concatenated. They should be

the same. And if they are the same they

were sold and delivered on the same

week. I'm going to commit on this. Once

I drag the measure in the visualization,

I get this particular number 22 299. How

do we even know that this particular

number is correct? Now, you can run the

same simulation in Excel, apply the

filters, filter the data and take a look

at the results. But why don't we take a

little information from the questions

that we are solving and try to write a

query instead so that you can also learn

how to write DAX queries and try to

validate the result. These are the exact

techniques that I talk about in my DAX

course. You'll not only learn how to

obviously frame the solutions to the

problems that I'm discussing in the

course, but more importantly, I pay a

lot of attention on explaining the logic

as to why a thing is working and why is

it not working and how do you actually

debug your own problems. This is going

to boost your confidence tremendously

while you're trying to build your own

solutions and you'll be able to

confidently build your solutions. In the

last few weeks, I have completely

revamped my DAX course and started from

scratch, teaching you the fundamentals,

adding in a lot of content depth to the

course. The new one is out now. I'll

leave a link for you to join the course

and you'll find it tremendously

beneficial. Let's just go back to the

video. All right. Now, moving on to

validation. How do you actually validate

it? So, what I'm going to do is all of

the work that I asked you to do in

Excel, like creating columns and

applying filters and all of all of that,

we're going to do that in the house of

PowerBI without actually cluttering our

data model. So I could have actually

gone ahead and in my sales table and

made the columns which is the week and

the year columns from the date here and

the delivery date here and I could have

applied the filters, carried the data to

Excel and done all of that work. But

let's just do that in the query view. So

I'm just going to hop over to the query

view right here which is where I can

write queries and I'm going to start to

write the query. So the very first thing

that I want to do is I want to define a

few columns. So I'll just use the define

keyword and then after that I'll use the

column keyword to define a column to a

table. So you write the define keyword

then what you want to define? I want to

define a column. Now, in order for us to

define the column, I have to write the

name of the table first, then write the

name of the column. This is mandatory.

So, I'm going to say, hey, I'm trying to

make a temporary column, not literally

like a physical column, like a temporary

column in the sales table. And the

column is going to be called as the

sales week here. I'm going to close the

square bracket. This makes the column.

And I'm just going to go ahead and

perhaps just write the number one and

just see if that is working or not. So,

I'm just going to say, hey, why don't

you just evaluate my sales table? So now

the sales table is going to show up this

column with a pseudo value one. Let's

just see if that appears or not. It just

takes a second and we have this pseudo

column one. Although this is not the

real column added to the data model.

This is just for the query purposes at

the moment. Now I can just modify this

value and write something else. The very

first thing that I want to write here is

nothing but what is the week number if

the week were to start on Monday. And if

I now click on run, sure enough it

actually gives me my week number that

week 29, week 37, so on and so forth.

That's nice. I then want to concatenate

that week with a little dash sign and

the year of that I'll just again click

on run and this is going to give me that

if the week were concatenated with the

year. This is how the results look like.

Now I have both of them calculated for

the sales date. Similarly I'm going to

define one more column and do that once

again for the delivery date for which

I'm going to go ahead and write the

column keyword once again. You can see

that there are a lot of underlines right

here. The column keyword syntactically

somehow is not supported at the moment

but kind of works. So I'm defining a

column and the column is going to be

called as delivery year a week year and

we are doing another column which is

where I'm trying to find the week number

which is starting on Monday for the

delivery date and again I'm trying to

find the year for the delivery date

concatenated with a little dash in

between and again evaluate my sales.

Click on run. We get another column

right here and that column is going to

be this particular column. Nice. Now

these columns aren't physically created.

Now it's the time to apply filters onto

these columns which is let's say week

one because what I wanted to validate

was that is this particular number

correct? If this is correct most likely

the week 2 is also going to be correct.

So what I want to see literally is that

with all my calculations and filters

applied do I get this number or not. All

right instead of evaluating the sales

table row I would now like to apply

filters on the sales tables and my

filter looks something like this. So I'm

just saying that hey why don't you

calculate the sales table with two

filters which is where the new column

that I have made which is the sales week

year which is this particular column

apply the week 1 and 2019 filter and on

the delivery week year apply one and

2019 as well. So that's my filter I'm

going to click on run the filter is

applied and at both the places I am just

left with this particular data now what

I want to do is technically I want to do

quantity into the unit price and find

out that if actually this gives me that

number or not. Now you can take this

data to Excel do the sum product of this

particular column and this particular

column and you're going to get this but

but we will continue working in the

query view and get to that answer that

we spoke about. So I'm going to go ahead

and say hey here is my table. I want you

to step inside every single row of this

particular table which is right here.

And in this particular table what I want

to do is quantity into the unit price.

So I'm going to say hey just pick up the

sales quantity and multiply that with

the sales unit price. I'm going to close

a bracket. Now the only problem with the

sumx calculation is that the sumx

function is going to translate this

entire table into a single number and

you can't evaluate a single number in a

table. So I got to wrap this around in

the curly brackets making it appear like

a studio table although it's not a

table. So I'm just going to say hey this

whatever number that you get off of the

single calculation please convert that

into a thing. And this actually gave me

an error. Let's just go find out what

the error is. All right the formula kind

of works here. I just formatted the

formula just a bit. There was a slight

error in the quantity spelling here. I

fixed that as well. And this all seems

to work. And now I can see the very

number that I was trying to fetch which

is 22,299

almost. And we have that number right

here. Let's just move on to level three

question. And this is very very

interesting and you're going to find a

lot of uses for this in your kind of

work. Take a look. So here I'm saying

that hey if the total sales was

approximately $400,000 and of the

$400,000 22,000 was delivered in the

same week then I want to know that how

was this entire $400,000 were delivered

in the following weeks that means this

is the delivery of week one and week one

that matches here that how much of this

was then delivered in week two and then

how much of this was delivered in week

three give me that spillover effect of

how the sales was delivered until all of

this total actually becomes $400,000.

How do we do that kind of thing? The

thing is that in order for me to do

this, we have to first understand that

how are we going to present this visual

in the first way. So the way that I'm

looking forward to present this is that

here in the rows I am going to have the

sales weeks that means once I am taking

a look at the totals right here the

total is going to tell me that how much

sales was made. Then if I take a look at

the individual numbers in the columns

right here these are going to be

delivery weeks. That means of this

particular sale, whatever that number

is, how much was delivered in week 1 and

then week two and then week three and

then week four, so on and so forth. This

is going to give me like a waterfall

like presentation. The only problem is

that at the moment, if I just go ahead

and for the quick second, if I just

happen to remove the sales delivered and

I just take my week again and put that

in the rows here, I'm not going to be

able to do that. So, I just convert that

into a matrix visualization. I put the

calendar week in the rows which is

technically filtering my sales order

date. I do that. This is my weeks. Now I

want to put the weeks once again in the

columns and I would not be able to do

that. So if I just click on add data and

if I go to my calendar table and I add

the weeks right here, you're going to

see that we would not be able to kind of

show the weeks right here because the

single column could either be in the

rows or it could actually be in the

columns. It cannot be at both the

places. So how do we solve the problem?

To be able to solve this problem, to

your surprise, we are going to create a

second calendar table. Yes, two calendar

tables that is going to allow us to take

one date to the sales date and the other

date to the delivery date. And that is

going to be magical and very, very

simple to solve. So, take a look.

Already got the calendar table here. I'm

just going to duplicate this particular

table. So, I'm just going to call a new

table right here. And I'm going to call

this particular table, hey, why don't

you make this table as my delivery

calendar? And this delivery calendar is

going to be whatever the calendar table

is. So, I'm just going to press enter. I

get another table called the delivery

calendar which is technically the

duplication of whatever the calendar

table is is what I get right here. Now

the only thing is that before I start to

build the relationship. I just have to

make sure that the week here the week

number is sorted in the week index

because I've made that column. Let's

just do that. I'm going to go right here

in the delivery calendar take my week

number which is week right here W 27 28

all of that needs to be sorted in this

particular order. So I'm just going to

go ahead sort by the week index. Now I'm

going to go ahead and start to build the

relationship. And the relationship that

I'm going to build is between the

delivery date. Remember that the visual

that we were trying to make that we had

week 1 and week 2 and then week three.

These weeks were sales weeks. That means

the filter is going to come from here

and filter the sales date. That's that.

And once we pick up this particular

filter and put that in the columns of

the matrix week 1 and then week 2 and

then week three. These filters are not

going to filter the sales date. These

weeks are going to filter actually the

delivery date. So we will take a look at

how the delivery was done. Literally

that that's it. No change whatsoever. So

I'm going to go ahead and take the

delivery date and link it right here.

That one to many relationship. This is

going to be an active relationship

because this is the only relationship

that exists. Pretty good. I'm going to

come back to the visual right here. I

made an empty visual which is where I've

applied a slicer 2019 and the month of

January so that I get to see less number

of weeks. And now I'm going to go ahead

and start to dump my week columns. So

from the calendar table I'm going to

dump the week column in the rows right

here. So let's just see do we have the

week in the rows? Yes, we have in the

columns I'm going to dump my uh delivery

calendar and the week right here and

these are my weeks and these are my

weeks. Nice. Now we will write the

simplistic calculation not write but

actually use the calculation that we

have already made. So if I just go ahead

and take my total sales calculation,

dump it right here, we have the answer

ready. Because if you think about it,

the weeks right here, when they apply

the filter from the second table to the

sales table, what you are applying the

filter on is the delivery date. And this

is how the items were delivered. And if

you total them all, all of these will

accumulate to $400,000. and in the next

week whatever you sold $46,000. This

particular entire sales of week 2 was

delivered like this in the following

weeks. This actually brings a very nice

waterfall kind of a layout which is

where you can trace the delivery or any

kind of movement of any object inventory

people across different time periods and

that is a very nifty way of doing that

using a modeling approach rather than

actually hitting it with tax. Now

obviously you can take this particular

calculation and make it nuanced, more

interesting and I talk about a lot of

that in my DAX course that how do you

enhance this calculation also make it

more visually appealing. In case you're

interested the link is down in the

bottom of the video. Please do join the

course and you will benefit a lot. Now

moving on to level four which is where I

want to talk about the most important

tax functions that you want to learn

first that is going to make learning DAX

probably enjoyable and a lot easier as

well. I'll see you in that video.

Cheers.

[Music]

[Music]