GET 15% OFF my Excel PowerPivot for beginners Course. Use discount code RUclips15 at checkout (Limited time offer!) tinyurl.com/4ayp2457 Visit my website: www.ExcelBonanza.com
Thank you for video, so easy and so informative. I came here specifically from the embedded video to Like it and Comment. We need to show our appreciation to people that share their knowledge to make life easier for the rest of us. I wholeheartedly Thank you!!!
Thank you very much for this excellent exposition. Can we adjust the measures to take the maximum year in the calendar table if no year is selected instead of showing "Please Select a Year"?
Hello! Great video! Thank you so much. Question for you - When showing the sales for previous week, it seems to only work when using 'week of year' and not the actual dates. Can you please provide a solve so I can show dates as opposed to week numbers?
I am having the same issue. My Day of Week values are not making sense in the visual. I have 2021 selected in my slicer and have Week number, Day of week in the rows. I also noticed in the PBIX you have a VAR for Last Week in the Sales Same Day Last Week Measure that is not in the video. I'm stuck at this point and not sure what to do next.
Thanks for this. Spent a week trying to get something like this to work and finally managed it! What woud I have to do to add YTD running total for last year based off the same kind of calculation as same week last year?
@@ExcelBonanza thank you..can you help me out in calculating both metrics ( DAX Measure ) i.e new week & Old Week as I have tried many refering Google links but wasn't successful
Hi Priya. Thanks for your comments. you will find the files on this link: bit.ly/3MbPi5k (20200417 Week Over Week Calculations in DAX) I have also updated the link in the video description
great excellent video well explained and it will be hopefully solving my problem, I want to see the last 8 or X weeks for a particular measure . do I use the topN () or can I put it in a measure? Thanks in advance
Hi Roger. Thanks for your question. you would need to calculate the measure during the whole x weeks. you would need to use the CALCULATE() function with DATESBETWEEN() function
This is a wonderful Video! I just have one question and really need your help with... How do you get the Dax measures e.g."SalesSameWeekLastYear" to show subtotals on a visual? Even in the video, the table totals for the Dax measures are blank.
Hi Tracy. You would need to test for the filter context having more than one week start date value. You can use the HASONEVALUE function for that. I explain that and many other calculations on my Powerpivot & DAX course of you are interested . You should find its link below the video.
Thanks so much for your amazing explanation! Could you tell me how to incorporate a product column into this calculation though? For instance, if I wanted to calculate the week on week growth of sales on a product level, the formula breaks down a little bit
This is an excellent lesson and solves a lot of problem for me as far as week time intelligence goes. I have a question though : I have a date table created in Power BI, using the calendar function. However I am not able to port it to PQ as you are doing nor do I see the "Edit Query" tab on my ribbon. Am I missing something here?
Hi. I think it's because you created the calendar table using DAX. In this case you can't port it to power query. In order to have it in power query, you need to build the calendar table in power query
Hi nice video my issue is that I am able to calculate week on week performance at overall year level but u want it for each product on the matrix table...example I have sales data set with mobiles sales so various mobiles like iPhone 13 Pro max and Samsung s22 5g , I want to calculate week on week sales for each model on matrix but when I do that I get week on week sales for full year I need by product...can you help me ?
Thank you sir, I have one question about the same week last year, the problem I have is there is 0 value for the Week 53 for 2020 leap year. can I please know how it can be Dax'ed to have it compared to Wk01 last year? thank you so much
Any insight on how to just do weekly time intelligence that allows the user to see changes week to week across all weeks, months and years? I currently have the functionality for week to week across months, BUT when I go to the beginning of any given year (ie. January, Year), I'm not sure how to code the logic for look back into the last week of the last month of the previous year (ie. December, Year). All Help appreciated
Hi Charles. Please check this video ruclips.net/video/JsVpNMqZor4/видео.html I explain the logic on this video and it's part of Powerpivot & DAX Course
hi, how can i make the same measure but to show customer sales previous week and previous 2 weeks, meaning the rows will be the customer name rather than the date or the week?
You need to capture the weeks as per the video and create a CALCULATE formula to calculate for these weeks and it should calculate it for the that particular customer as long as you don't change the customer filter context in CALCULATE
@@ExcelBonanza you mean like this CALCULATE ([the measure created in the video],values (customer))? Then put the customers on the row and the new measure in the vlaues
Hi. Really love the video. Its really informative. For me, I want to count the no. of order and compare Week on Week, I tried to use COUNTROWS in the formula to replace calculate([Sales amount] but I get an error. Can you show me what I need to change in the formula to count rows instead of calculate sales amount? Appreciate your advice on this.
Great video, but specifically for the first measure (comparison to previous week), is there a way to write the DAX so that it will work with just the "Week Start" in a table (or on an axis). For example, how would you adjust this visual so that you could put Week Start, Total Sales, and Total Sales LW in one table?
Hi Avi.. I have only week.year as textcolumn and value coming from fact table..I need to calculations like complete previous year.. I don't have date column. If 15. 2022 to 19.2022 is selected as cycle, then last year sales should be from sum of value 1.2021 to 52.2021. How can I achieve that pls..pls help me.....I got stuck............
One of the issue I found with this. e.g. if the start date of week is between 26th Dec 2022 - 1st Jan 2023, the week number could be both Week 53 of 2022 and Week 1 of 2023, then measure result goes wrong.
How can I make a custom week range, for example. i want my week classification to start every Friday to Thursday rather than the Sunday to Saturday that Power BI has. Any idea please.
When you create the week number column in Power Query, you can change the starting day of the week. You use the Date. WeekOfYear function and you can set the starting day of the week.
There's a video on my channel for the introduction to Powerpivot where I show how to build a Calendar table using M. I would recommend you watch it and build your calendar table this way
Yeah, Week to date can be easily done. Just capture the week start date in A variable and capture today in a variable too then Result = Calculate ([Sales Amount, All(tblCalendar), DATESBETWEEN(tblCalendar[date], Week Start Date (that you captured in a variable), Today() ) But this is gonna change depending on today, so be mindful of that.
@@ExcelBonanza Hi...The LastMonthPY and LastWeekPY are not working for me. For example, in Jan 2021, the result i'm getting as prev month is Jan 2020 value. But I should be getting Dec 2020 value. Same with prev week, getting 2020 week 1 value as in 2021 week 1, instead of 2020 week 53 value. Pls. help.
![Noob To Max With DRAGON REWORK In Blox Fruits [FULL MOVIE]](http://i.ytimg.com/vi/LnBMOinoOvA/mqdefault.jpg)
GET 15% OFF my Excel PowerPivot for beginners Course. Use discount code RUclips15 at checkout (Limited time offer!)
tinyurl.com/4ayp2457
Visit my website: www.ExcelBonanza.com
This video is way underrated. This provided exactly what I needed!
Thanks, Brandon! Please make sure to like the video and share it with your friends so that we can reach more people :)
Finally I got my solution here. Thank you so much for the video. It really helped me
Thanks for your comment, Manish! I'm glad that you found the video useful!
Thank you for video, so easy and so informative. I came here specifically from the embedded video to Like it and Comment. We need to show our appreciation to people that share their knowledge to make life easier for the rest of us. I wholeheartedly Thank you!!!
Thanks, Sandhu!
I'm really happy that you found the video helpful. And I'm so happy to read your Comment :)
Thank you. This helped me not bang my head against the keyboard anymore. So simple when you really look at it.
Thanks Alan! Great to know that the video has helped you!
Thank very much for your video, it was very easy to get the week of month!
My pleasure!
This was so helpful. Thank you so much!
Glad it was helpful!
Thank you very much for this excellent exposition. Can we adjust the measures to take the maximum year in the calendar table if no year is selected instead of showing "Please Select a Year"?
Yes. You can
Hey mate, thanks so much for this. However I was not able to have any data populate and its blank. Wonder why this is the case?
Hi Julian. Can you please elaborate more? I don't understand your question
Hello! Great video! Thank you so much. Question for you - When showing the sales for previous week, it seems to only work when using 'week of year' and not the actual dates. Can you please provide a solve so I can show dates as opposed to week numbers?
You can add the week start date column to your calendar and then put it in the rows section.
I am having the same issue. My Day of Week values are not making sense in the visual. I have 2021 selected in my slicer and have Week number, Day of week in the rows. I also noticed in the PBIX you have a VAR for Last Week in the Sales Same Day Last Week Measure that is not in the video. I'm stuck at this point and not sure what to do next.
Thanks for this. Spent a week trying to get something like this to work and finally managed it! What woud I have to do to add YTD running total for last year based off the same kind of calculation as same week last year?
Sorry. I don't understand your question. can you please explain more?
Hi can you please assist me in getting measure for wow variance
It's simply (New week - Old week) / old week
You can also use (New week / Old week) - 1
@@ExcelBonanza thank you..can you help me out in calculating both metrics ( DAX Measure ) i.e new week & Old Week as I have tried many refering Google links but wasn't successful
Hi Arpit. You can take my Powerpivot and DAX course. I explain that in detail on the course
Hi there, very informative lesson, how can I get the dataset , I did download the powerBI but still looking for dataset (CSV files )
Hi Priya. Thanks for your comments. you will find the files on this link: bit.ly/3MbPi5k (20200417 Week Over Week Calculations in DAX)
I have also updated the link in the video description
great excellent video well explained and it will be hopefully solving my problem, I want to see the last 8 or X weeks for a particular measure . do I use the topN () or can I put it in a measure? Thanks in advance
Hi Roger. Thanks for your question. you would need to calculate the measure during the whole x weeks. you would need to use the CALCULATE() function with DATESBETWEEN() function
Hi Excelawy
I am asking about how to calculate the sales amount when i choose specific date from the start of the week of that chosen date
You can do that if you have a "Week Start Date" column in the Calendar table
This is a wonderful Video!
I just have one question and really need your help with...
How do you get the Dax measures e.g."SalesSameWeekLastYear" to show subtotals on a visual? Even in the video, the table totals for the Dax measures are blank.
Hi Tracy. You would need to test for the filter context having more than one week start date value. You can use the HASONEVALUE function for that. I explain that and many other calculations on my Powerpivot & DAX course of you are interested . You should find its link below the video.
Thanks so much for your amazing explanation! Could you tell me how to incorporate a product column into this calculation though? For instance, if I wanted to calculate the week on week growth of sales on a product level, the formula breaks down a little bit
You would need a filter for Product on CALCULATE
This video is a Godsend!
Thank you! I'm glad that you liked it!
This is an excellent lesson and solves a lot of problem for me as far as week time intelligence goes. I have a question though :
I have a date table created in Power BI, using the calendar function. However I am not able to port it to PQ as you are doing nor do I see the "Edit Query" tab on my ribbon. Am I missing something here?
Hi. I think it's because you created the calendar table using DAX. In this case you can't port it to power query. In order to have it in power query, you need to build the calendar table in power query
Hi nice video my issue is that I am able to calculate week on week performance at overall year level but u want it for each product on the matrix table...example I have sales data set with mobiles sales so various mobiles like iPhone 13 Pro max and Samsung s22 5g , I want to calculate week on week sales for each model on matrix but when I do that I get week on week sales for full year I need by product...can you help me ?
You would need to put the product on the columns section of the matrix
Thank you sir, I have one question about the same week last year, the problem I have is there is 0 value for the Week 53 for 2020 leap year. can I please know how it can be Dax'ed to have it compared to Wk01 last year? thank you so much
I have a solution. I will need to post another video for it. Stay tuned
Thank you sir. its really informative season. just one question how do we get total of previous week
Need to explain that in another video. You will need to iterate over all the results using SUMX()
Any insight on how to just do weekly time intelligence that allows the user to see changes week to week across all weeks, months and years? I currently have the functionality for week to week across months, BUT when I go to the beginning of any given year (ie. January, Year), I'm not sure how to code the logic for look back into the last week of the last month of the previous year (ie. December, Year). All Help appreciated
Hi Charles. Please check this video
ruclips.net/video/JsVpNMqZor4/видео.html
I explain the logic on this video and it's part of Powerpivot & DAX Course
hi, how can i make the same measure but to show customer sales previous week and previous 2 weeks, meaning the rows will be the customer name rather than the date or the week?
You need to capture the weeks as per the video and create a CALCULATE formula to calculate for these weeks and it should calculate it for the that particular customer as long as you don't change the customer filter context in CALCULATE
@@ExcelBonanza you mean like this CALCULATE ([the measure created in the video],values (customer))?
Then put the customers on the row and the new measure in the vlaues
No, it's CALCULATE ([Measure], ALL(Calendar), Week = previous week)
@@ExcelBonanza i tried this and it works only if the weeks are in rows, if customers are on rows it gives blank
Do you have the customers table connected to your fact table? Is there a relationship? Does the customers table filter your sales/ fact table?
Hi. Really love the video. Its really informative. For me, I want to count the no. of order and compare Week on Week, I tried to use COUNTROWS in the formula to replace calculate([Sales amount] but I get an error. Can you show me what I need to change in the formula to count rows instead of calculate sales amount? Appreciate your advice on this.
You need to create a measure by calculating the number of rows and then replace Sales amount with that measure
hi, thanks for this. How we can do this in excel?
You will need to import the data to the data model and use Powerpivot
Great video, but specifically for the first measure (comparison to previous week), is there a way to write the DAX so that it will work with just the "Week Start" in a table (or on an axis).
For example, how would you adjust this visual so that you could put Week Start, Total Sales, and Total Sales LW in one table?
You can calculate the sales LW as a measure
Hi Avi.. I have only week.year as textcolumn and value coming from fact table..I need to calculations like complete previous year.. I don't have date column.
If 15. 2022 to 19.2022 is selected as cycle, then last year sales should be from sum of value 1.2021 to 52.2021.
How can I achieve that pls..pls help me.....I got stuck............
You would need to have a calendar table with a date column. it's essential for time intelligence calculations.
this was very helpful, I'm still learning this... Can you tell me the measure for the sales amount as well?
It's order quantity * unit price
One of the issue I found with this. e.g. if the start date of week is between 26th Dec 2022 - 1st Jan 2023, the week number could be both Week 53 of 2022 and Week 1 of 2023, then measure result goes wrong.
You can change the formula in power query so that you would have week 53 and then week 2 of the new year, and you would skip week 1
How to calculate working days for alternate week off?
This is brilliant boss :)
can you please confirm why totals are not coming?
It's because we haven't put a condition to handle them in the DAX formula. I explain that on my Powerpivot and DAX course.
How can I make a custom week range, for example. i want my week classification to start every Friday to Thursday rather than the Sunday to Saturday that Power BI has. Any idea please.
so, when i make my week numbers, its based on these week range and not the typical weeks
When you create the week number column in Power Query, you can change the starting day of the week.
You use the Date. WeekOfYear function and you can set the starting day of the week.
I am working with DATEAUTO
There's a video on my channel for the introduction to Powerpivot where I show how to build a Calendar table using M. I would recommend you watch it and build your calendar table this way
Thank you very insightful. What of a week to date measure
Yeah, Week to date can be easily done. Just capture the week start date in A variable and capture today in a variable too then
Result = Calculate ([Sales Amount,
All(tblCalendar),
DATESBETWEEN(tblCalendar[date],
Week Start Date (that you captured in a variable), Today() )
But this is gonna change depending on today, so be mindful of that.
Excel Bonanza modifying my date table. Will revert with feedback
Sure. No problem
@@ExcelBonanza I am not getting this
What are you not getting?
Can we download the (pbix) file please?
Here's the link: bit.ly/3dfXNNc
Thank you 🙏🏾.
My pleasure!
@@ExcelBonanza Hi...The LastMonthPY and LastWeekPY are not working for me. For example, in Jan 2021, the result i'm getting as prev month is Jan 2020 value. But I should be getting Dec 2020 value. Same with prev week, getting 2020 week 1 value as in 2021 week 1, instead of 2020 week 53 value. Pls. help.
How to compare Order Change with respect to same day last week in excel
It's explained on the video
Starts at 2:58
Can you share sample PBIX files
here's the link : bit.ly/3qnpOrL
thanks
My pleasure