DAX Fridays

In today's video, we are going to go through how to think about Time Intelligence calculations with a specific case: Calculating the number of open orders using DAX.
I read a million years ago a blog post from Gerhard where he explained how to do it and even though we are not going to use the same DAX syntax or functions , we will use his methodology:
blog.gbrueckl.at/2014/12/even...
Here you can download all the pbix files: curbal.com/donwload-center
SUBSCRIBE to learn more about Power and Excel BI!
/ @curbalen
Our PLAYLISTS:
- Join our DAX Fridays! Series: goo.gl/FtUWUX
- Power BI dashboards for beginners: goo.gl/9YzyDP
- Power BI Tips & Tricks: goo.gl/H6kUbP
- Power Bi and Google Analytics: goo.gl/ZNsY8l
☼☼☼☼☼☼☼☼☼☼
POWER BI COURSES:
Want to learn Power BI? How about you take one of our courses? Here you can find the available courses:
curbal.com/courses-overview
☼☼☼☼☼☼☼☼☼☼
ABOUT CURBAL:
Website: www.curbal.com
Contact us: www.curbal.com/contact
▼▼▼▼▼▼▼▼▼▼
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:
curbal.com/product/sponsor-me
Many thanks in advance!
▲▲▲▲▲▲▲▲▲▲
************
************
QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
Linkedin ► goo.gl/3VW6Ky
Twitter ► @curbalen, @ruthpozuelo
Facebook ► goo.gl/bME2sB
#CURBAL #SUBSCRIBE

[@himanshshakk3083]

what a clear cut explaination dear thank you

[@CurbalEN]

😃

[@bettina.e-k]

Thank you Ruth, a perfect demonstration of "DAX is all about the filters" 😁 and using Variables for troubleshooting 😊 PLUS really useful technique!!

[@dhanicole]

Thank you Ruth! I spent a few hours figuring out the right formula in excel. Then I found this video and this worked perfectly!!! You are awesome!!!

[@annaephgrave1345]

Thank you so much ... I have been struggling with this for ages, and your explanation has made everything just click into place 🙂

[@CurbalEN]

Wonderful!!

[@mat.frazer]

Thank you. As soon as you started with the green dots my headache was listed and there was sense again in this world. Now to continuing watching the video.

[@CurbalEN]

Wait till you see the purple ones 😂

[@MrSpiritUAll]

Looks so easy when you explain! 😘 Love it!

[@CurbalEN]

He made it easy for me, I am just passing the knowledge over :)

[@thegrilow]

That’s Great Ruth! Thank you!!! 😊

[@sue_bayes]

Thank you and makes perfect sense using the Cross Filter

[@msheaver]

Boom! Mind blown! @5:06, the light bulb went off in my mind, and this is indeed genius!

[@CurbalEN]

👏👏👏

[@WilliamThomas2040]

Very helpful! Thank you!

[@kennethstephani692]

Great video, Ruth!

[@CurbalEN]

Thanks, that blig post helped me tremendously when I got started!!

[@mcnater]

Love the video thanks.

[@stuartmccloud307]

Great Video, thanks. And I never realised you could zoom the text in the formula bar till now :)

[@CurbalEN]

I should do it more often ;)

[@josefedericopresno6668]

Great video and very helpfull. Thanks a lot!

[@CurbalEN]

Fab to hear!!

[@rerangelt]

Ruuuth! Como siempre, muy educativo.

[@CurbalEN]

Gracias!!

[@annarozyc5184]

great video thank you !!!

[@gayatrilshinde1560]

This what I looking for👍

[@yornav]

Thanks for the clear video. Is there a way to show at a point in time only orders that are open for more than x days? I'm trying to make a report that shows in a chart the number of tickets that are open for 30 days or more.

[@pahan95]

Thank you!

[@michelleryan8512]

This is great! How would I alter this to count distinct? Thank you

[@analynoclarit564]

Thank you for this! Wondering how I can apply this in showing requests or orders than went beyond and within cut off for our management?

[@LandscapeInMotion]

Love the idea! What software do you use to record your vids?

[@CurbalEN]

Camtasia

[@fredbarnes196]

This is fantastic, thank you. I think I understand that the CROSSFILTER is removing the relationship to CALENDAR. If you had multiple relationships from ORDER to CALENDAR you would have to remove those as well or the MONTH field will be bucketing on the UnCROSSFILTERED relationship. Is that right?

[@jarnopronk5419]

Hi Ruth @curbal, thank you so much for this video. It really helps me with my report. I have one question. I think this measure only looks to the orders that actually have a shipdate. When the orders do not have a shipdate (Orders that are still open) are not in this measure. So when you want to see the open orders of last week, it probably is not as valid as the data of 2020. Can you help me with this?

[@gmartinezm]

Awesome video, what if I want to see in a chart, all the orders Opened (open date) and Closed (closed date) by week, the challenge im facing is that using table calendar with active relationship to active orders, sometimes is not coming back that week with the closed orders (inactive relationship). Not sure why closed orders are not coming back that week. Thoughts?

[@ljubicar1987]

Very cool video. 👍 I would only like to ask you to increase the size of visuals, it is hard to see when watching on the phone. Like you did when you were writing a formula, that was easy to see.

[@CurbalEN]

I try to remember that, but sometimes... will continue trying ;)

[@NeBixAt]

Hi Ruth, do you prefer CROSSFILTER instead of ALL function in this case? What is your recommendation?

[@CurbalEN]

It is more about the model and data when deciding which one to use. Test all the options ( removefilters, all, crossfilter...) and check which one performs better for you.

[@bhanuprakash-ql7ut]

Hi Curbal can you please expalin bit clearly in relationship model ,what all relationship to be made

[@Astronomynatureandmusic]

Unfortunately I cannot find correct content for #208 in your download center. It says "Userrelationship with slicers".

[@yasinsen6301]

What about date/time format and categorizing by month? Trying to figure it out for 2 months without any luck.

[@bryanhaynie7377]

Will this dax work if not all orders have a shipped date?

[@hannafilipowska7962]

very helpful video, thank you! however I would like to ask - is it possible to use this expression with inactive relationship using "USERELATIONSHIP" function? the following expression I wanted to create doesn't work: CLOSED = var MaxDate=Max('calendar'[Datetime submit]) var MinDate=Min('calendar'[Datetime submit]) return CALCULATE( COUNTROWS( FILTER(events, events[closed_date]=MinDate )) , CROSSFILTER('calendar'[Datetime submit], events[closed_date], None), USERELATIONSHIP('calendar'[Datetime submit],events[closed_date]))

[@mattmatt245]

Working with overlapping time periods is much easier in Excel then in Power BI

[@jayt64]

How do i incorporate a distinct count ? Instead of countrows

[@xodrinker]

As always 👍

[@CurbalEN]

😊

[@frankgovers8420]

Nice, cross filter, first and second item, may switch. Date or order date does not matter

[@CurbalEN]

Thanks!

[@EbenezerSarfoAdu]

Thanks but can't find the PBIX files

[@CurbalEN]

The pbix file to get is the one in Gerhard's post :)

[@user-nu4xt6zp8y]

I tried it but I see the same number of cases for all months, anyone that had the same issue?

[@stephano9543]

name of the file pls?

[@CurbalEN]

Get the file from Gerhard post, he has proper data for you to play with.

[@stephano9543]

Ty so much for the video. I'm waiting for next video to learn how to get the color on the table

[@CurbalEN]

Yes, I was thinking to publish it already next week, but I think it will be on 2 weeks instead, but it is coming :)