DAX Fridays!

Подписаться 135 тыс.

Просмотров 35 тыс.

50% 1

Are you also bummed out that there is no weekly time intelligence in DAX? Believe it or not, creating weekly calculations is easier than you think, and I show you how in this edition of #DAXfridays.
#DAX #Powerbi #curbal
Are you in a hurry? Skip the talk and go right to it:
01:06 Calculate Weekly Sales in DAX
01:46 Calculate Previous Week Sales in DAX
06:25 Calculate Same week last year in DAX
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
************
************
QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
Linkedin ► goo.gl/3VW6Ky
Twitter ► @curbalen, @ruthpozuelo
Facebook ► goo.gl/bME2sB

Опубликовано:

5 авг 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 117

@cassandrasimmons4830 4 года назад

I spent around 5 hours today trying to figure out how to do a waterfall chart comparing previous weeks to each other. I read so many blogs, articles and forums. NO ONE was addressing the same issue I was referring to, nor had a solution. THANK GOD I found you! So simple! you saved me!

@CurbalEN 4 года назад

Music to my ears!! ;) /Ruth

@lunaplanet12 3 года назад

This is very helpful. It solved my problem. And thank you so much for explaining in such an easy way to follow and understand. I appreciate the way you explained what each every part of the formula does and why a certain part doesn’t work and how to fix it. I learned a lot more about Dax from that. Shout out to you!

@MrToxiclaughter 3 года назад

You've helped me so much these last months, I always recommend people to watch your channel if they want to learn about PBI. God bless you

@CurbalEN 3 года назад

Thanks!!

@christianrodriguez4617 3 года назад

Thank you very much!!! You have no idea how easy is to understand everything with your videos!

@CurbalEN 3 года назад

Music to my ears!!

@TheTushar231990 3 года назад

Thank you son much for this video. 🙏 you have been so helpful. God bless you. Keep on posting power bi videos...

@garimakumar5628 4 года назад

Thanks, Ruth! You make it look so simple :) Keep up the good work!!

@CurbalEN 4 года назад

Sometimes it is :) /Ruth

@enriquedominguez9709 5 лет назад

Clear Explanation, Thank you Ruth!

@CurbalEN 5 лет назад

Thanks Enrique! /Ruth

@implementedgmbh 5 лет назад

Great Explanations! Thanks Ruth!

@CurbalEN 5 лет назад

Thanks again :) /Ruth

@jaypillay8380 2 года назад

Always such a help, making learning easy!

@CurbalEN 2 года назад

Yay!!

@ravindrapinapaka6582 3 года назад

Nice explanation, good job Ruth

@k0708870 2 года назад

Great simple explanation as always! Thankyou :)

@CurbalEN 2 года назад

☺️☺️

@javierguerrero9253 5 лет назад

Very good video Thank you so much for sharing it!

@CurbalEN 5 лет назад

Thanks for the feedback!! /Ruth

@oliverbuckley5121 5 лет назад

Thank you Ruth. I like to show the week as a weekend date rather than a week number so that it’s clearer for the user. To calculate the same week last year, subtract 364 days.

@CurbalEN 5 лет назад

Thanks for sharing Oliver! /Ruth

@AshikKhan 5 лет назад

Hi Ruth, You create magics..... That was awesome, I will try the same...thanks

@CurbalEN 5 лет назад

Thanks! DAX is magical, once you start cracking the code :) /Ruth

@jochendecraene5519 4 года назад

Thanks a lot Ruth, this helped me out, again ;-)

@CurbalEN 4 года назад

You’re welcome 😊 /Ruth

@Minifini4 3 года назад

Thanks a lot! Super helpful! Any suggestion on how to show totals for PW & SWPY measures?

@danmota83 4 года назад

It was really, really helpfull. Thanks a lot!

@CurbalEN 4 года назад

🥳🥳

@RobertoStaltari 5 лет назад

Easy...after Ruth's video! Thanks! :)

@CurbalEN 5 лет назад

😂😂 Thanks Roberto! And have a great weekend :) /Ruth

5 лет назад

So helpful! 👏🏻 👏🏻 👏🏻 👏🏻

@CurbalEN 5 лет назад

Thanks!!! 😊 /Ruth

@MelissaCornfield 3 года назад

Thank you so much! this helped so much.

@CurbalEN 3 года назад

Yey!! 🥳🥳

@user-se9ct4yh1w 3 года назад

thank you very much. you save my life as usual :)

@CurbalEN 3 года назад

At your service :)

@marasamrs7103 3 года назад

Thank you. You just saved my wknd in 9:30 mins

@CurbalEN 3 года назад

🥳

@alialhadikobeissy4863 3 года назад

BIG THANKS!

@CurbalEN 3 года назад

You welcome!

@entstuff381 4 года назад

It is solved my problem.. thanks Ruth :)

@entstuff381 4 года назад

Every time you like my reply and I am feeling very lucky :)

@CurbalEN 4 года назад

Same here when my works helps you! Stay safe now:)

@luuk9659 4 года назад

Thanks!

@CurbalEN 4 года назад

You welcome! /Ruth

@65419279 4 года назад

Hi, congrats for the explanation, it is very good. The formula DAX in fact works, however i would like to show the total of column, i realized that it is not summarizing, could you help about it? Thank you.

@hernandiazdelcastillof8223 4 года назад

Thanks Ruth, this was very Helpful, it Has worked. I have a question you might help me. Once I have those measures I have created a Line chart where I want to show the last 10 weeks and a "Parallel" line with the data points from Las year. However in order to see those data points from last year I cant Unfiltered those periods, therefore I would have them in the left of the graph and in parallel with latest weeks data. How can I solve it?

@mshparber 5 лет назад

First - like, then - watch!

@CurbalEN 5 лет назад

😂😂, that made my day, thanks! /Ruth

@mukeshpanchal5762 3 года назад

This is great video. Quick question : I have week starting date in the table (Week starting with sunday). I want to skip every other week starting date and display in the chart . How do i add filter that?

@leonelalegria7787 4 года назад

Hello! Great vídeo! How can I make a Calculate (with average, for exemple) for the last week? Thanks!

@stephenbrincat7124 5 лет назад

Thanks Ruth, One question, if you can help? I receive the data only monthly basis (ex. I currently have till March 2019) when I do this measure I'm getting data till wk 17/2018 and wks 14-19/2019 are blank. since I don't have the data yet, is there a way to filter SWLY only to end March? Thanks and happy weekend.

@CurbalEN 5 лет назад

Yes, everything is possible with DAX ;) Could you post on the power bi community to get detailed help!? Thanks and happy Friday! /Ruth

@HT_Peace7 4 года назад

Thanks a lot, I have one question how to put week one and week two records in one

@fbseugvfusbfkj 2 года назад

First of all thanks for sharing your knowleadge, i have a doubt what happen with the last week of december vs first day january , I tried this method but the code doesn't do the calculation

@FletcherMcmullen 3 года назад

Good stuff...what is the difference doing it this way vs using SAMEPERIODLASTYEAR? I have had a hard time getting the same week last year calc to work until I saw this.

@fegaru 5 лет назад

Hi! Why the New Measure doesnt summarise? In your example "Sales" has his total, but "Sales PW" doesnt. Thanks!

@Chuchoman21 2 года назад

Hello, if I want to filter by a specific product that I have in my data source, how can i make the DAX code for this?

@miragliag 3 года назад

Hi Ruth, On the Measure you built SameWeek Last Year there is not Total showing in the Table. Is there a Trick how to modifiy DAX that also it shows a total of this measure? Thanks for your thoughts in advance!

@chrisa8203 4 года назад

Hi Ruth! Is it possible to do average of past 53 weeks?

@pl4195 2 года назад

I have one question. I make one sales report everyone with one excel as data source. How can I save all these weekly report in the same power BI report so I could look back previous report by clicking the filter.

@ybarsabal 3 года назад

Ok, this makes sense. BUT what if you want to allow the user to filter/slice for one or more dimensions?

@abdelrahmanabdou8905 4 года назад

Hi Ruth, Thanks for the video! I'm trying to solve a problem where I need to extract the total sales per calendar date where it is for the same week and same weekday. So for example for the year 2020, the 1st of January is the Wednesday of the first week, and if I need to compare it to the same day in 2019, I would be comparing it to the 2nd of January 2019 as it is also the Wednesday of the 1st week. My weeks are Sunday to Saturday. If you can help me out with that problem and create a video about it, It would be much appreciated, and I believe it would be a good compliment to this video as well! Thanks again, and keep up the great work!

@abdelrahmanabdou8905 4 года назад

Hi Ruth. I managed to Figure it out! I still think it would be a good idea for a video. Thanks!

@nseworld5521 4 года назад

Sometimes i wanna see formulas, wich Power BI uses to calculate current week sales : how can i calculate total sales of the max week? ( current week/ or the last week in table "sales")

@siddheshamrutkar8684 4 года назад

Hello Ruth, it was nice explaination but was having one concern lets say if the week number for this year 2020 is 1 and same period last year also 1 that case it will substract and we will get 0 and then the sales value will be blank? So how to handle those situation in such cases. I hope you understand.

@TheTushar231990 3 года назад

Can you please tell me if I can create dynamic wtd with this. If yes how? It's quite urgent I am on my deadline.

@RehanShaikh-ip9hh 2 года назад

Hi Ruth, How to make table for only 7 days of current week & PYSW...

@Daria_Katunina 2 месяца назад

Thanks for this video, but I see problem showing totals, so I cannot use this solution in KPI´s. Maybe you can give a hint how to solve this problem ?

@shoaibrehman9988 2 года назад

Hi can you calculate weekly sales by dax rather then drag and drop in table/matrix

@Ajuneja99 4 года назад

Thanks for the video Ruth. I was trying to implement this but it won't work for the 1st week of the year, correct me if I'm wrong. Thanks

@josecrespo6630 4 года назад

You can find the solución hete: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-jclWnA7pEvY.html

@Hunk829 4 года назад

hi Ruth that was awesome, but what happens if somebody wants to see sales LW for the 1st week. Ideally it should be 52nd week of the last year... right? but with your measure it'll be blank

@CurbalEN 4 года назад

The purpose of the video was to explain weekly calculations, you can easily adjust the measure to account for the year shift. /Rurh

@davidcadman3562 5 лет назад

Hi. Great, though does PW work in the case of Wk 1? To me it seems the criteria would be Wk No -1 and the year to filter should be selected Year -1.

@CurbalEN 5 лет назад

If Week no =1 then 52 else my expression. Have a nice weekend :) /Rutg

@jaimeildefonsosegurapena7328 4 года назад

@@CurbalEN Ruth, years can have 53 or even 54 weeks (leap years starting on Sunday), therefore I think you'll need to "calculate" the last week of previous year.

@CurbalEN 4 года назад

True, there are a lot of IFS when creating calendars ;) /Ruth

@somily800 4 года назад

I have one problem, with this i need to see year, month and week as a hierarchy and compare with the same period last year at the level of year, month and week

@fegaru 5 лет назад

Hello!!! Please help me!! I´m using PowerPivot Excel, and for some reason "SelectedValue" doesnt exist. Can you tell me how can I do this in Excel? Thank you very much!!!

@CurbalEN 5 лет назад

Hi, SELECTEDVALUE is just syntax sugar for IF ( HASONEVALUE ( Table[column] ), VALUES ( Table[column] ) ) :) /Ruth

@fegaru 5 лет назад

@@CurbalEN Great! Thank you very much!!!

@andrecavasotero6234 5 лет назад

Time intelligence in DAX always surprises me.

@CurbalEN 5 лет назад

DAX always surprises me ;) /Ruth

@toshiehabu1102 4 года назад

Thanks Ruth for all your tips. I try to copy what you do in the video. But the previous week's value shows exactly the same as the current week. What wrong with me! No of 'Value' is the moment of time number therefore it should not be summarised. But to be able to put the Value into this DAX example, I added 'SUM'. Sales PW = CALCULATE([Vale Mesure for cal], FILTER(ALL('Dates'), 'Dates'[Year]=SELECTEDVALUE('Dates'[Year])&& 'Dates'[Week No] = SELECTEDVALUE('Dates'[Week No])-1) ) Thanks very much

@bijilashobanabalakrishnan7718 2 года назад

Hi Habu, I am facing the same issue. How did you resolve it? Could you please help me?

@jorgeldv8661 4 года назад

Hola Ruth, desde el confinamiento, he mirado mucho tus videos, son geniales. En este, en concreto, he intentado solucionar lo de los totales en blanco con: IF(HASONEVALUE('Calendar'[Week Number]);CALCULATE([Uds.];FILTER(ALL('Calendar');'Calendar'[Week Number]=IF(HASONEVALUE('Calendar'[Week Number]);VALUES('Calendar'[Week Number]);0) && 'Calendar'[Year]=IF(HASONEVALUE('Calendar'[Year]);VALUES('Calendar'[Year]);0)-1));CALCULATE([Uds.];DATEADD('Calendar'[Date];-1;YEAR))) pero no calcula bien los totales. Lo hago desde el power pivot, por eso utilizo if(hasonevalue... Quizás falte algún filtro más. Enhorabuena por todo tu esfuerzo y el trabajo que haces...!!!

@jorgeldv8661 4 года назад

Perdona, el criterio de las fechas es distinto, por eso el resultado, da distinto.... :((((

@peterg4130 3 года назад

What happens when you are in Week 1 and you want to see sales for previous week (in this case it would be week 52, previous year)... What happens then? Because doing - 1 would equal to zero.

@CurbalEN 3 года назад

Add an if condition for that.

@soy34mb 5 лет назад

Hola Ruth, muy buen video, muchas gracias. Una sugerencia, creo que sería bueno que cambies el micrófono, ya que se escucha muy bajito el volumen :).

@CurbalEN 5 лет назад

Gracias Jimmy. En que PC ves los vídeos? /Ruth

@soy34mb 5 лет назад

Para ser honesto, los trato de ver en el transcurso de 1 hora q tengo de casa al trabajo, y cuando pongo el celular en altavoz casi no se escucha y es algo que no me pasa con otros videos que veo durante ese mismo transcurso.

@CurbalEN 5 лет назад

Ah, es en el móvil? Un Android? En mi móvil se oye bien. Te pregunto para poder reproducir el error. /Ruth

@soy34mb 5 лет назад

Así es, un Android, el Honor Play, pero como te digo, con otros videos no me pasa, te recomendaría que escuches 1 video a todo volumen y luego uno de los tuyos a todo volumen para que notes la diferencia.

@CurbalEN 5 лет назад

Vale gracias :) /Ruth

@alejandrogonzalezbueno8044 5 лет назад

Muy buen vídeo! Sería interesante que comentarás que esta formula que estás utilizando no te permite comparar la primera semana del año con la ultima del anterior, es decir, semana 1 1998 con semana 52 1997. ¿Podrías explicar como realizar esto con DAX? Muchas gracias!

@CurbalEN 5 лет назад

Con un if() se soluciona fácil verdad? /Ruth

@alejandrogonzalezbueno8044 5 лет назад

@@CurbalEN podrías hacer un ejemplo o comentar cómo sería la fórmula? Muchas gracias!

@CurbalEN 5 лет назад

Si la semana es =1 entonces semana 52 sino ... la formula que escribi. /Ruth

@alejandrogonzalezbueno8044 5 лет назад

@@CurbalENAhora lo entendí! Muchas gracias!

@CurbalEN 5 лет назад

Perfecto!! /Ruth

@negisaab74347 3 года назад

It doesn't not give previous week sales for week 1

@jimmyni1983 5 лет назад

Hi @Ruth. First thank you for this amazing tutorial. Now, maybe I'm doing something wrong, but I cannot see file 124: Weekly time intelligence in Power BI in your DAX Fridays Drive curbal.synology.me:5000/d/f/433570902335034553. Is this the correct place to find it? Thanks again and nice weekend!

@CurbalEN 5 лет назад

Oh, no! Did I forget? Sorry, I will upload it tomorrow. /Ruth

@jimmyni1983 5 лет назад

Curbal NP, @Ruth. I’ll wait then. Thank you.

@CurbalEN 5 лет назад

I am sure I uploaded it, but I am guessing to the wrong server. Upgrading my infrastructure at the moment... /Ruth

@jimmyni1983 5 лет назад

@@CurbalEN Relax @Ruth. Happy to give you a heads up then :) I'll download this file in specific to test something else. For example... When you created the measure SWLY I want to test the previous measure and apply SAMEPERIODLASTYEAR instead of using ... 'Calendar'[Year]=SELECTEDVALUE('Calendar'[Year]) -1 && ... I don't know if it'll work as I think it will. :)

@CurbalEN 5 лет назад

Uhhh , I see where you are going! ... tomorrow you will get it or reproduce it with any of other files if you can’t wait ;) /Ruth