DAX Fridays #191: Create a last invoice calculation: the process, calculation and troubleshooting

So, how do you need to think when creating a DAX measures? Do you create the measure in one step? Do you break it down in small steps? How do you troubleshoot it?

In today's video I am going to show you the steps you need to take to create a DAX measure and troubleshoot it. The measure is : last invoice date by customer and contract.


SUBSCRIBE to learn more about Power and Excel BI!

Our PLAYLISTS:

☼☼☼☼☼☼☼☼☼☼

POWER BI COURSES:

Want to learn Power BI? How about you take one of our courses? Here you can find the available courses:

☼☼☼☼☼☼☼☼☼☼

ABOUT CURBAL:

▼▼▼▼▼▼▼▼▼▼

If you feel that any of the videos, downloads, blog posts that I have created have been useful to you and you want to help me keep on going, here you can do a small donation to support my work and keep the channel running:


Many thanks in advance!

▲▲▲▲▲▲▲▲▲▲


************





************


QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
Twitter ► @curbalen, @ruthpozuelo

#CURBAL #SUBSCRIBE

[pilarbaldominos]

Thanks Ruth..it is very useful when you explain potential problems and how to think in order to fix it and no only the answer

[thomasivarsson]

One of the best presentations i have seen of how to think when you use DAX.

[nguwah]

Thank you so much. I'm finding about this case. Now I know because of you.

[abhishekstatus_]

Thanks Ruth! As always learning something from you every week ! Have a great weekend ahead !:)

[juansepowerplatform]

thanks for this video, this is the type of logic that takes you from making Dax work to understanding Dax and building more robust formulas.

[ralfwitte]

Thank you, Ruth. Brilliant explanation!

[sandeepreddy]

Thanks Ruth as always I learn something new for every video

[andysuzhou]

Thanks for the great vid, as always, love your channel.
This does the job too:
Last invoice date =
CALCULATE(
MAX(Projects[InvoiceDate]),
FILTER(
ALLEXCEPT(Projects, Projects[Project]),
Projects[Customer] = Projects[Customer]))

[qazq]

At the outset, I want to say that your videos always bring something valuable. Your step-by-step guide to measuring is awesome.

An additional next step that we should mention is checking how our measure works (Is it really fast? Maybe there is a better solution).

However, I have some comments as to the solution used.
Let's not use "LASTDATE" here as it returns Tables. The function MAX should be used instead of LASTDATE when the result must be a scalar value instead of a table.
We can do it more efficiently.

We get the same result with the following query:

CALCULATE (
MAX ( 'Table'[Invoice Date] ),
ALL ( 'Table' ),
VALUES ( 'Table'[Customer] ),
VALUES ( 'Table'[Contract nr] )
)

The results from DaxStudio -> queryPlan are interesting.
Your solution is 274 row long (physical query plan - because of for example unpaking lastdate table etc), but this above need only 15 row.

Shortest = better

@Filip Wiński
his answer is even better :)

[borjaveiga]

Thanks Ruth! I finally understand and you solved one of my biggest doubts. Mil gracias ;)

[joangarcia]

Me han gustado mucho tus consejos sobre como plantear un problema y un resultado inesperado. Buen fin de semana.
/Joan

[filipwinski]

Hello Ruth, nice video. My formula:
Last invoice date =
CALCULATE(
MAX('Table'[Invoice date]),
All('Table'[Invoice date])
)

[nawaazr]

Brilliant tutorial Ruth, thank you. Really interesting to follow your thought process. I had a similar problem a few weeks ago, where I needed to compare the latest date with the second-latest date for a unique ID (if the second-latest date was within a year of the latest, then the data would be categorised differently). I came across the same issue where the function was filtering only one row. I ended up creating a new column which filtered the max(date) for each ID. I then created a measure with an if function and ID = EARLIER(ID), and compared the date in each row with the max(date). I'm thinking now that I could have used ALL() instead- it could have made this simpler.

[A.H-ms]

Amazing ! as Always ! Need a small advice Ruth, can you please guide what path I shall follow in learning more about PowerBI.

I am an intermediate level user of PowerBI - well conversant with visualization, some basic DAX functions & relationships within tables.

I am little confused on which path { what to be learned after what} I should follow in order to enhance my skills.

Look forward to your feedback.

PS : Planning to go for DA-100 very soon :)

[Theshashidharpatil]

I started learning DAX just to solve exactly this problem! I am really enjoying learning DAX and your videos are really helpful in that regard. Thank you!

I wanted to ask you if it is possible to do the following. Let's suppose, there is another column which tells the quantity of products each customer buys. I need a calculated column to show the quantity of last product bought by each customer. How would I go about it?

[jakeduddy]

Can also use summarize to create the filter tuple and remove filter on dates to be verbose
measure=
CALCULATE(
MAX(Contracts[Invoice Date])
, SUMMARIZE(Contracts,Contracts[Contract nr], Contracts[Customer])
, REMOVEFILTERS(Contracts[Invoice Date])
)

[DzinoRC]

Hello,
Thank you for useful video. I try to apply measure from this video to my TimeSheet table for daily work-time calculation, but without success.
I have TimeSheet table where is work start time and work end time for each user, day and tasks. I want to create a measure for daily work-time as difference between start time of first task per User/Day and End time of last task per User/Day. Adaptation of your formula for my case is perfectly working when I select only 1 day in calendar:
Daily Work-Time = CALCULATE(
DATEDIFF(MIN(TimeSheet[TaskStart]), MAX(TimeSheet[TaskEnd]), MINUTE),
FILTER(ALL(TimeSheet),
TimeSheet[Date] = &&
TimeSheet[User ID] = SELECTEDVALUE(TimeSheet[User ID]) &&
TimeSheet[Task Status] = "Completed"
)
)
Could you please help me to modify measure to work when multiple days are selected? For example one month?

[amirhd]

And the next step make it flag to conditioning format to add color or something difference

[borkasiamese]

Sorrry, no offence, but at first I thought you were in a barrel :) Thanks for the class!