DAX Fridays!

[@cassandrasimmons4830]

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]

Music to my ears!! ;) /Ruth

[@MrToxiclaughter]

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]

Thanks!!

[@christianrodriguez4617]

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

[@CurbalEN]

Music to my ears!!

[@oliverbuckley5121]

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]

Thanks for sharing Oliver! /Ruth

[@lunaplanet12]

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!

[@TheTushar231990]

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

[@marasamrs7103]

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

[@CurbalEN]

🥳

[@ravindrapinapaka6582]

Nice explanation, good job Ruth

[@Minifini4]

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

[@garimakumar5628]

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

[@CurbalEN]

Sometimes it is :) /Ruth

[@k0708870]

Great simple explanation as always! Thankyou :)

[@CurbalEN]

☺️☺️

[@jaypillay8380]

Always such a help, making learning easy!

[@CurbalEN]

Yay!!

[@AshikKhan]

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

[@CurbalEN]

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

[@agentcheesecake]

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

[@CurbalEN]

At your service :)

[@implementedgmbh]

Great Explanations! Thanks Ruth!

[@CurbalEN]

Thanks again :) /Ruth

[@alialhadikobeissy4863]

BIG THANKS!

[@CurbalEN]

You welcome!

[@fbseugvfusbfkj]

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

[@javierguerrero9253]

Very good video Thank you so much for sharing it!

[@CurbalEN]

Thanks for the feedback!! /Ruth

[@enriquedominguez9709]

Clear Explanation, Thank you Ruth!

[@CurbalEN]

Thanks Enrique! /Ruth

[@MelissaCornfield]

Thank you so much! this helped so much.

[@CurbalEN]

Yey!! 🥳🥳

[@entstuff381]

It is solved my problem.. thanks Ruth :)

[@entstuff381]

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

[@CurbalEN]

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

[@65419279]

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.

[@danmota83]

It was really, really helpfull. Thanks a lot!

[@CurbalEN]

🥳🥳

[@jochendecraene5519]

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

[@CurbalEN]

You’re welcome 😊 /Ruth

[@wwelover247]

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.

[@miragliag]

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!

[@shoaibrehman9988]

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

[@mukeshpanchal5762]

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?

[@Chuchoman21]

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?

[@RehanShaikh-ip9hh]

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

[@Daria_Katunina]

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 ?

[@RobertoStaltari]

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

[@CurbalEN]

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

So helpful! 👏🏻 👏🏻 👏🏻 👏🏻

[@CurbalEN]

Thanks!!! 😊 /Ruth

[@abdelrahmanabdou8905]

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]

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

[@ybarsabal]

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

[@mshparber]

First - like, then - watch!

[@CurbalEN]

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

[@HT_Peace_be_with_you]

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

[@hernandiazdelcastillof8223]

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?

[@chrisa8203]

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

[@leonelalegria7787]

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

[@pl4195]

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.

[@TheTushar231990]

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.

[@fegaru]

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

[@nseworld5521]

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")

[@jorgeldv8661]

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]

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

[@luuk9659]

Thanks!

[@CurbalEN]

You welcome! /Ruth

[@Ajuneja99]

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]

You can find the solución hete: kzbin.info/www/bejne/oJTPiKF3bNV4rLs

[@siddheshamrutkar8684]

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.

[@andrecavasotero6234]

Time intelligence in DAX always surprises me.

[@CurbalEN]

DAX always surprises me ;) /Ruth

[@Hunk829]

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]

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

[@toshiehabu1102]

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]

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

[@stephenbrincat7124]

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]

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

[@davidcadman3562]

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]

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

[@jaimeildefonsosegurapena7328]

@@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]

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

[@soy34mb]

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]

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

[@soy34mb]

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]

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

[@soy34mb]

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]

Vale gracias :) /Ruth

[@alejandrogonzalezbueno8044]

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]

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

[@alejandrogonzalezbueno8044]

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

[@CurbalEN]

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

[@alejandrogonzalezbueno8044]

@@CurbalENAhora lo entendí! Muchas gracias!

[@CurbalEN]

Perfecto!! /Ruth

[@peterg4130]

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]

Add an if condition for that.

[@somily800]

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]

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]

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

[@fegaru]

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

[@negisaab74347]

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

[@jimmyni1983]

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]

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

[@jimmyni1983]

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

[@CurbalEN]

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

[@jimmyni1983]

@@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]

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

[@fernandoreis8457]

You're so pretty