***** Related Links ***** Compare Time Periods With Non Standard Date Tables - DAX & Power BI th-cam.com/video/g_3eLaKgeEQ/w-d-xo.html Placing Workday And Weekend Day Numbers Into The Date Table In Power BI blog.enterprisedna.co/placing-workday-and-weekend-day-numbers-into-the-date-table-in-power-bi/ Calculate Consecutive Weekday & Weekend Days In Power BI Using DAX blog.enterprisedna.co/calculate-consecutive-weekday-weekend-days-in-power-bi-using-dax/ ***** Related Course Modules ***** Time Intelligence Calculations portal.enterprisedna.co/p/time-intelligence-calculations Advanced Data Transformations & Modeling portal.enterprisedna.co/p/advanced-data-transformation-and-modeling Mastering DAX Calculations portal.enterprisedna.co/p/mastering-dax-calculations ***** Related Support Forums ***** Getting Last Week (shift specific) Data To Display In A Table forum.enterprisedna.co/t/getting-last-week-shift-specific-data-to-display-in-a-table/3523 Same Period Last Year for Fiscal Week Nbr (weeks in sales table are non standard) forum.enterprisedna.co/t/same-period-last-year-for-fiscal-week-nbr-weeks-in-sales-table-are-non-standard/240 Filter A Table By The Aggregation Of Its Self forum.enterprisedna.co/t/filter-a-table-by-the-aggregation-of-its-self/4754 For more weekly sales queries to review see here….. forum.enterprisedna.co/search?q=weekly%20sales
Hi Amit Pradhan, glad you appreciated the video. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Here’s the link: th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
Hi ManticOmar11, glad that you appreciated our content! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI and Power Platform video tutorials and announcements. Cheers!
Thanks for your video. I just used DATEADD from the beginning of your video. I also use a WeekEnding date instead of week numbers as they get cut off. WeekEndingSat= Dates[Date] - WEEKDAY(Dates[Date], 1) + 7. From here, I just use -7 DAY on the WeekEnding field, and ALL(Dates[Date]) for dates filtered by the slicer: Hours_LastWeek := CALCULATE([Hours], DATEADD(Dates[WeekEndingSat], -7, DAY), ALL(Dates[Date]))
Hi Anil, glad that you appreciated our content! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI and Power Platform video tutorials and announcements. Cheers!
HI. I using this formula on two separate tables. One table contains a list of new applications and value I am counting are applications of type 'X'. The TW to LW count comparisons are bang on. But when I try the same calculation on another table that holds all the enrollments (calculation if a 'Distinct' row count, the LW number are off. They are always more that the TW value.
This is wonderful and I was able to get this to work for me. However, when I want to selected multiple weeks in my Slicer, it gives me a (Blank). Any pointers or another video you can refer me to?
Hey Ashton, We're not too sure about how you're evaluating the results and in what context because in our PBIX file, the measure is working perfectly fine and yielding the results when multiple "Week & Year" are selected into the slicer. It's a bit difficult to assess and give recommendation without looking at the data structure, model and working of the PBIX file for the reference. You can reach out to us on our Community Forum by providing a detailed description of your query along with relevant files for reference. Our members and team of Experts will be happy to help out! If you haven't yet, do subscribe to our TH-cam channel and LinkedIn group to keep posted on Power BI, Power Platform, and the Microsoft stack updates. Hoping you find this useful! Cheers, Enterprise DNA forum.enterprisedna.co/ www.youtube.com/@EnterpriseDNA www.linkedin.com/groups/12004506/
Hi! Thank you for video. Advise for newbies as I am)) I had an issue with CALCULATE formula with FILTER function. I couldn't use any other column with the exception of "dates" column which was set in ALL function. That happened due to the fact that I wrote not only the name of the table 'calendar' but also I specified parameter with a column name, so in the next steps formula didn't allow me to use something else except for dates.)
Thank you for your point of view on that! I tried (also tried some other solutions that I saw here on comments) and I guess I found a new way of doing the same, but with fewer lines: PREVIOUS SW = CALCULATE(SUMX(VALUES(Dates[Date]);CALCULATE(SUM(Sales[Sales]);DATEADD(Dates[Date];-7;DAY))))
might be a problem if you run this on the 2nd or 3rd day of the week, gives you wrong result. The one used here is better suited for day to day comparison.
Hi Sam First I would like to congratulate you on a number of very good videos then I have a question on how to create 10 weeks back I guess that I should modify your code in 2 different places so both the week I check and the number of weeks has to go 10 weeks back as well? Hopefully like I have done below? Visitors -10w = VAR CheckWeek =SELECTEDVALUE('Date'[APWeek]) VAR CheckYear =SELECTEDVALUE('Date'[APYearNo]) VAR MaxWeekNumber = CALCULATE (MAX('Date'[APWeek])) RETURN SUMX( FILTER(ALL('Date'); IF(CheckWeek = 10; 'Date'[APWeek] = MaxWeekNumber && 'Date'[APYearNo] = CheckYear - 1; 'Date'[APWeek] = CheckWeek - 10 && 'Date'[APYearNo] = CheckYear)); [Visitors]) regards Jørgen
Hi Jørgen, Thanks for commenting. Great to see you applying the patterns into your work. That’s the great thing about DAX so many of the patterns are reusable. Your calculation makes sense if your data model is det up correctly. Be sure to check out the Enterprise DNA forum for any specific queries. forum.enterprisedna.co/ Regards Enterprise DNA
Hello Juan Manuel, thank you for posting your query onto our channel. Well, our channel is mainly focused with the Power BI contents so we don't have much of an with the Excel Power Pivot functions. For furthermore queries, you can also reach out to us onto our Community Forum where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference. Hoping you find this useful. Happy Learning!!! forum.enterprisedna.co/
Thanks Sam, this is great ;-) I have another way I want to put this. How do I label my customer overdue orders so I can see how old they are. so I want to see the value of my current month orders, previous month 2 months ago, 3 months, 6 months, 1 year or longer. Them I want to put that in a list and see the individual amounts add up to the total. Can you help me with this? I figure I need a column with a calc, but I'm struggling to get the formula correct
I did exactly what you did and the table works, but why doesn't this data go nicely into a line chart, ex, this week by day vs previous week by day? Am I missing something?
Thank you for sharing this great video!! I've searched for days but finally got what I was looking for! One question: what to do with inconsistent weeks? for example my weeks are: 03/2021, 05/2021, 06/2021, 07/2021 etc. how can I make week 5 refer to week 3 value? Thank you!!!
Hello Rae Chaud, thank you for posting your query onto our channel. We're providing few of the links of the articles from our blog posts as well as links of the videos available onto our Enterprise DNA TH-cam channel pertaining to the topic where we've inconsistent calendar or date table in our scenario and how to achieve the results. For furthermore queries, you can also reach out to us onto our Community Forum where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference as well. Hoping you find this useful and meets your requirements that you've been looking for. Happy Learning!!! blog.enterprisedna.co/compare-time-periods-with-non-standard-date-tables-dax-and-power-bi/ blog.enterprisedna.co/common-time-intelligence-patterns-used-in-power-bi/ blog.enterprisedna.co/year-to-date-sales-for-power-bi-custom-calendar-tables/ blog.enterprisedna.co/calculating-month-on-month-change-custom-calendars-power-bi-and-dax/ blog.enterprisedna.co/the-dateadd-function-the-best-and-most-versatile-time-intelligence-function-in-power-bi/ community.powerbi.com/t5/Community-Blog/Unique-Time-Intelligence-Analysis-Examples-for-Power-BI/ba-p/1085176 forum.enterprisedna.co/t/non-standard-time-intelligence/10452 th-cam.com/video/g_3eLaKgeEQ/w-d-xo.html th-cam.com/video/aODroF37pq0/w-d-xo.html forum.enterprisedna.co/
Sam - it was much needed video. I hit with the same issue and solved myself recently.. however I couldn't implement YTD,Mtd..etc on top of last week sales measure.. would be great if you could extend this series to it
thank you for this. I will definitely have a stab at it. I have a situation where I have different products and our week in KSA starts on Sunday and ends on Thursday so weekend is on Fri and Sat. How would I measure daily changes?? would appreciate it if you could help me out in this. I would put that formula within the model if possible when transforming the data from my excel source file so that I can focus later on only on visualising and analysing
Hello Ahmad Jaber, thank you for posting your query onto our channel. We're providing a link below from our Community Forum where similar query was asked by a member about how to start the week on "Saturday" whereas in your case it's on "Sunday". Just follow the steps as showcased into the link and you'll be able to achieve the desired results. For furthermore queries, you can also reach out to us onto our Community Forum where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference as well. Hoping you find this useful and meets your requirements that you've been looking for. Also make sure that you're subscribed to our TH-cam channel so that you don't miss out on any updates pertaining to the Power BI. Happy Learning!!! forum.enterprisedna.co/ th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html forum.enterprisedna.co/t/date-table-fiscal-week-start-saturday/14909/7
Hi Sam, Thank you for the valuable information, but unfortunately when I implement the same steps to my model I received the following error "DAX comparison operation do not support comparing values of Type text with values of Type integer" and I'm sure that non of my values are text..!! any help or ideas in this strange issue. Regards
Hi Abdullah, Thank you for posting your query onto our channel. We really appreciate you taking your valuable time to post it. Well it's always a little bit difficult to judge and provide the results without looking at the data structure, model and working of the PBIX file for the reference. And so we're not in a position to judge or provide comment on anything. Therefore we encourage you to please write back to us onto our Community Forum by providing the proper description of the problem that you're facing along with the PBIX file for the reference as well as the mock-up results that you're trying to achieve where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference. The only thing is, there's has to be some sort of text value which is getting compared with the integer and so it gives this kind of error. Hoping you find this useful. Also, make sure that you're subscribed to our TH-cam channel so that you don't miss out on any updates pertaining to the Power BI. You can also join our Power BI group on LinkedIn to receive latest updates on Power BI. Below are the links provided for the reference purposes. Cheers, Enterprise DNA forum.enterprisedna.co/ th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html www.linkedin.com/groups/12004506/
@Sam Mckay, Thank you so much for this video, however, i do have few questions that Im not able to accomplish. 1) Here in this video, You are showing year and week wise in the Rows but how can we show the previous week sales bases on dimension like City, Product etc. 2) I work Excel 2016, and I do not see "selected value" Dax function. How can I solve this. Please help
Hello Mohammad, Thank You for posting your query onto our channel. Well for the first question, you cannot simply show the previous week sales or be it any number evaluated based on time intelligence calculations just on the basis of dimensions like City, Product, etc. The reason being is, they'll only be able to evaluate the results just for those dimensions and that too for the entire period unless and until date context is transferred or provided into those visuals either by dragging date field directly into those visuals alongwith the City or Product dimension or any date dimension is selected in the form of slicer. You got to provide date context in any form in order to evaluate and achieve this results. As pertaining to the second question, you can raise this query onto the Excel Community forum where excel experts will be able to assist you. For furthermore queries, you can also reach out to us onto our Community Forum where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference as well. Hoping you find this useful and meets your requirements that you've been looking for. Also make sure that you're subscribed to our TH-cam channel so that you don't miss out on any updates pertaining to the Power BI. Happy Learning! Thanks and Warm Regards, Enterprise DNA forum.enterprisedna.co/ th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
Hi Sam, Superb video and you're making the people to understand in a best way with good logic. I am a die Hard fan of your DAX logic and almost follow your foot steps. I just have a quick question and seeking help for the same from you. I have a sales data set on hourly basis for four year and need to understand how do i calculate the previous year's date and hours sales calculation. I am using the Previous year sales data to compare with current year sales data. I have working on E-Commerce website analytics and using power bi for building dashboard and Reports. Your kind help with solution is appreciated.
First you will want to break out the time and dates into separate column usually. Then use time intelligence function to calculate what you require. Give this a go first and I think you'll see the end game clearer this way.
Hi, Thank You for sharing this video. It is really helpful. I would like to know how I can calculate the difference sales between two dates? I have tried the function which you gave mentioned in this video at 0.54 sec. I am not getting the result for monday (5th Aug). In my data I have values till 2nd aug ($109,111) and after that 5th Aug($98,655). I want the difference between these dates as well. Can you please help me reagrding this?
Hi Sam, Great Video, very clear, it works great for me, but when I incorporated the categories (food, beverages), the previous week expression showed the sum of hole week instead of the sales of that particular category previous week.... what I am missing out? regards! Matias
Finally after a lot of struggle found this solution of if you keep categories or anything into filter. Instead of using calculate(sales,filter(all(table1)) use calculate(sales,filter(allselected(table1))) this would give sales value with respect to filter you place. Another way is you can still use the calculates(sales,filter(all(table1),table1(category)=min(table1(category))&&quarterno=min(quarterno)&&yearno=min(yearno))
Hello SUNIL NALLAGONDA, thank you for posting your query onto our channel. Well same concept is applicable for week 52 as well as week 53. If you scroll through till the end you'll witness that for the year 2017 there were 52 weeks and the results are calculated accordingly. Also we're providing a link below that contains several posts pertaining to the M Code which shows the results according to the different calendars applicable in different scenarios. For furthermore queries, you can also reach out to us onto our Community Forum where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference as well. Hoping you find this useful and meets your requirements that you've been looking for. Happy Learning!!! forum.enterprisedna.co/c/M-Code-Showcase/38 forum.enterprisedna.co/t/cumulative-weekly-sales-measure/437 blog.enterprisedna.co/calculate-previous-weeks-sales-advanced-dax-in-power-bi/ forum.enterprisedna.co/
Hi - this works really well except when there are no sales for a particular week but there are for the previous week. In that case both the previous week and the current will show blank when the previous week should have a value. This is because the SELECTEDVALUE is not returning anything for the current week (VAR CurrentWeek = SELECTEDVALUE(dimDate[WeekNumber]). Is there a way around this?
Hi Sam. Thanks for your super-informative video that was thoroughly well-explained. I'd like to know how to compare this year's sales in WeekNumber vs. the same WeekNumber last year. E.g. week 3's sales in 2021 vs. week 3's sales in 2020, and so on. I would deeply appreciate it if you would kindly assist, please. The idea would be to reflect the results in 2 views: 1) like in your video with the column charts, and 2) as a line chart where the full year of 2020 is visible and 2021 year-to-date is visible as a separate line. I hope to hear from you on this. Thanks
Hello Andrew Newey, thank you for posting your query onto our channel. Well, after calculating the Weekly Sales you can compare it with the last year by using the "DATEADD()", "SAMEPERIODLASTYEAR()" or "PARALLELPERIOD()" functions. It's always a little bit difficult to judge and provide the results without looking at the data structure, model and working of the PBIX file for the reference. But still you can go through some of the videos available pertaining to this concept which are also available onto our Enterprise DNA TH-cam channel. Also we encourage you to please write back to us onto our Community Forum by providing the proper description of the problem that you're facing along with the PBIX file for the reference as well as the mock-up results that you're trying to achieve where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference. Hoping you find this useful. Happy Learning!!! forum.enterprisedna.co/
Hi Sam, I have tested and used this formula and it works great. I have a slight problem when using the same logic but instead calculating for Last Month instead of last week. it basically gives me 0 for every January occurrence. Could you pinpoint to me as to what i might be missing. This is my logic below HardwareSalesLM = var CurrentYear = SELECTEDVALUE(DimDateCalendar[Year]) var CurrentMonth = SELECTEDVALUE(DimDateCalendar[MonthNum]) var MaxMonthNumber = CALCULATE(Max(DimDateCalendar[Month]);ALL(DimDateCalendar)) Return IF(SUMX( FILTER(ALL(DimDateCalendar); If (CurrentMonth = 1; DimDateCalendar[MonthNum] = MaxMonthNumber && DimDateCalendar[Year] = CurrentYear -1; DimDateCalendar[MonthNum] = CurrentMonth - 1 && DimDateCalendar[Year] = CurrentYear)); [TotalHardwareSales]) = BLANK();0 ; SUMX( FILTER(ALL(DimDateCalendar); If (CurrentMonth = 1; DimDateCalendar[MonthNum] = MaxMonthNumber && DimDateCalendar[Year] = CurrentYear -1; DimDateCalendar[MonthNum] = CurrentMonth - 1 && DimDateCalendar[Year] = CurrentYear)); [TotalHardwareSales])) Thanks
In case week numbers are not in sequence order ,how do we achieve previous week sale data pls help sir.. for example week numbers in my table are below and I need to calculate sales for previous week based on week number selected on slicer. Week number 10 13 14 16 20 28
I have a situation like this, for calculating MoM and WoW, I need to consider same period in the previous year. Let's say if I want to return MoM for August 2021, then calculation would be like (Aug_2021 sales - Aug_2020 sales)/Aug_2020 sales * 100. Like this it should be dynamic for MoM and WoW and in the view I need to display like Last 12 months for MoM % growth, and Last 13 months for WoW % growth. I achieved the calculation but not able to restrict last 12 months/13 weeks. Any help on this would be helpful. Note: we are not supposed to use calendar table as per our requirement. So considering sales_date from data itself and I achieved WoW and MoM based on month_number and Week_Number. And based on maximum sales_date, we need to display last 12 months or Last 13 weeks.
Hello Govardhan Bola, Thank you for posting your query onto our channel. Firstly, "Date/Calendar Table" is a mandatory requirement in order to have a time-intelligence results and without that you won't be able to achieve the desired results. Below is the link provided for reference about why date table is important to have into the data model. Also it's always a little bit difficult to judge and provide the results without looking at the data structure, model and working of the PBIX file for the reference. And therefore we encourage you to please write back to us onto our Community Forum by providing the proper description of the problem that you're facing along with the PBIX file for the reference as well as the mock-up results that you're trying to achieve where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference. Hoping you find this useful and helpful. Also, make sure that you're subscribed to our TH-cam channel so that you don't miss out on any updates pertaining to the Power BI. Cheers, Enterprise DNA forum.enterprisedna.co/ th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html forum.enterprisedna.co/t/basic-date-table-power-query-m-function/19055 forum.enterprisedna.co/t/extended-date-table-power-query-m-function/6390
Amazingly well executed tutorial. I wonder if I can still add a question here. How can one modify the formula to fetch the sales in similar logic "Two weeks ago". I tried to simply changing it to Dates[ISO WeekNumber] = CurrentWeek - 2 && Dates[Year] = CurrentYear ) ), -2 , but then it wont be able to succesfully fetch the number from last year, but otherwise it works
Hello B0ge, Thank you for posting your query onto our channel. Well, this is where the situation gets trickier because just by changing the logic from " - 1" to " - 2" will not actually derive the correct results in that case you'll have to modify the context into the formula to achieve the results. Below is the measure provided for the reference that will get you correct results for Previous Two Week Sales - Sales P2W = VAR CurrentWeek = SELECTEDVALUE( Dates[Week Number] ) VAR CurrentYear = SELECTEDVALUE( Dates[Year] ) VAR MaxWeekNumber = CALCULATE( MAX( Dates[Week Number] ), ALL( Dates ) ) RETURN SUMX( FILTER( ALL( Dates ), IF( CurrentWeek = 2, Dates[Week Number] = MaxWeekNumber && Dates[Year] = CurrentYear - 1, IF( CurrentWeek = 1, Dates[Week Number] = MaxWeekNumber - 1 && Dates[Year] = CurrentYear - 1, Dates[Week Number] = CurrentWeek - 2 && Dates[Year] = CurrentYear ) ) ), [Total Sales] ) For furthermore queries, you can also reach out to us onto our Community Forum where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference as well. Hoping you find this useful and meets your requirements that you've been looking for. Also, make sure that you're subscribed to our TH-cam channel so that you don't miss out on any updates pertaining to the Power BI. Cheers, Enterprise DNA forum.enterprisedna.co/ th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
Hi, great video! I have just one question, maybe you could help me out a bit. I have daily data from 2014 onwards, and your measure works great, except in one case - when I have for example W1 of 2019, your measure for previous week is not returning anything because the last week in 2018 was W52. I think the problem is that the MAX Week Number is 53, and not 52, so when the previous year is standard and has 52 weeks, then your solution will not return anything for the first week for the current year. In the dataset you presented in the video, for the first week of 2018 measure should return blank since 2017 had 52 weeks. Can you help me find a solution for this problem? Maybe something is wrong with my data or my calendar?
I have just recently learning DAX, and I am amazed how extremely cumbersome this it when compare to excel formula (where you can just use a helper column and sumifs), or am I missing something?
Hello kerry r, thank you for posting your query onto our channel. Well this is the reason why it's always said "DAX is simple but not easy". We sincerely don't recommend to use of the helper columns where the the work can be simply done using the measures. Because more the number of the columns into the data, the larger will be the size of the data more. We provide the free beginner's course of the "Power BI and DAX". We encourage you please go through those free courses in order to strengthen the fundamentals of Power BI and than you can also upgrade to the premium courses that we offer at a subsidized rates which offers great deal of benefits. Below is the link of the beginners course provided for the reference. For furthermore queries, you can also reach out to us onto our Community Forum where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference as well. Hoping you find this useful and helps you in your learning!!! Happy Learning!!! portal.enterprisedna.co/courses/enrolled/157239 portal.enterprisedna.co/courses/enrolled/195681 forum.enterprisedna.co/
You could potentially do that also. I think that setting it up like this is probably a better solution to align to a custom date table. But if -7 days does the trick also, I don't think you can go wrong. Chrs
I have a field on my date table for week ending: _dcWeekEndingSat = _dtDateFilter[Date Filter] - WEEKDAY(_dtDateFilter[Date Filter], 1) + 7. From here, I just use -7 DAY on the WeekEnding field, and ALL(_dtDateFilter[Date Filter]) for dates filtered by the slicer: _dm_Vol_Handling_Total_Hours_LastWeek = CALCULATE([_dm_ToT_Handling_Total_Hours], DATEADD(_dtDateFilter[_dcWeekEndingSat], -7, DAY), ALL(_dtDateFilter[Date filter]))
Superb explanation! I notice though, both "Prior week Sales" measures do not have a Grand Total. Would you mind explaining why the Power BI (or DAX) does not generating them? If it's possible to generate them, would you mind sharing how to do so ? I appreciate it!
Hello Vijay, Thank You for posting your query onto our channel. Well it's always a little bit difficult to judge and provide the results without looking at the data structure, model and working of the PBIX file for the reference. And therefore we encourage you to please write back to us onto our Community Forum by providing the proper description of the problem that you're facing along with the PBIX file for the reference as well as the mock-up results that you're trying to achieve where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference. Hoping you find this useful. Also make sure that you're subscribed to our TH-cam channel so that you don't miss out on any updates pertaining to the Power BI. Happy Learning! Thanks and Warm Regards, Enterprise DNA forum.enterprisedna.co/ th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
Hello Sam, good talking to you again, the second part is giving an error regarding the data types, what could be reason??? I am subscriber from your channel and I do have access to all your TV resources. Instead of using totals sales I am using the total claims.
Hello Sam, Great Video...I dont understand so well what you mean witn a potencial error with the week 52 and 53....are you goint to show that case?...Thnak you ...this is a great channel!!
Hi, Great video! Thanks a lot. But I have an issue. The measure "Sales PW" goes well, except several weeks in the table. I have 2017-2019 data. All is ok except year- 2018, wk 2-11. At that time 2018/19, wk 2-11, is ok. Can you give some hint how to solve it. Thanks for your help in advance. :-)
Hi, I'm trying to use this formula in my calculations, but it gives me the error of: A single value for column 'WeekNumber" in table 'Date' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum, to get a single result. What could possibly be the reason for this? Thanks beforehand for responding, I'm loving your videos!
After adding measures, Weekly Diff & Weekly Diff %, could Sales PW filtering be used to get previous week's Weekly Diff % in order to get the Weekly Growth (Expanding & Contracting) by using Weekly Diff % in the SUMX?
Hi, i need help i am working with power pivot on excel 2016, the function selected value is not available, do you know if there is any substitute of the function in power pivot for excel 2016?
Hi, I have a problem when trying to calculate weekly sale of a particular customer, then compared with previous week. So I have Name, Date, Sale. So I want to calculate how much the sale of particular customer, then compared to previous week. Could you provide some inside? Thank you.
Hello baonguyen1705, thank you for posting your query onto our channel. Well it's always a little bit difficult to judge and provide the results without looking at the data structure, model and working of the PBIX file for the reference. And therefore we encourage you to please write back to us onto our Community Forum by providing the proper description of the problem that you're facing along with the PBIX file for the reference as well as the mock-up results that you're trying to achieve where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference. Hoping you find this helpful. Happy Learning!!! forum.enterprisedna.co/
Thank you so much..I wanted this but when I am trying to get previous week value for each store, I am getting the average value of all stores in the previous week instead of store by store value. Can you please help me with this? For example: I have Store Week44-Value Week45-Value A 20%. 30% B. 40%. 40% If 45 is my current week, The previous week value for store A will be 20% and that of store B will be 40%...But I am getting same value for store A and store B 30% which is the average of store A and Store B in week 44 Please help me Thanks
Great! How is it possible to put 2 equals signs inside of an IF statement ? In the true and false part...don’t see the logic. Looks like your assigning a variable?
Great ... Comparing Weeks not that easy, especially if you have years with 52 and 53 weeks. Would be great if you could do some more calculations based on calendar weeks. Thanks for this video.
Hi Chris A, You could quite easily extend this pattern and for average or std dev. The key is ensuring you have a good date table. You then need to change the context of your calculation to evaluate for the 53 weeks you are interested in. Remember there is no Week concept in time intelligence within power bi you therefore need to do the conversion via days. Be sure to checkout the Enterprise DNA forum for any specific queries. forum.enterprisedna.co/ Regards Enterprise DNA
Hello, I have 3 years of data, the first year have 52 weeks of payments, the second year has 53, so when I applied the logic you explained here is using 53 as the maxweeknumber. Could you give us an example in how to overcome that issue? Thanks
I found a way to make the maxweeks work with var maxweeksprioryear = CALCULATE(CALCULATE(MAX('Calendar'[WeekNumber]),'Calendar'[WeekYear]=currentyear-1),ALL('Calendar'))
what about calculating day on day change but when there is not activity on saturday and sunday. So each week ends with friday and the next row is monday unless there is a public holiday. So how to do day on day % change
I had a category as a condition too. So I did 2 calculated columns - one for a sequence of date by the category and one for the value for the last sequence order using FILTER, ALLEXCEPT and EARLIER -1. And then a calculated measure to do a % change between the 2 columns. As a calculated measure EARLIER was not picking up column names...Unless I am missing a trick here..
Yes that's fine, but not necessarily all the time. Some date calendars within businesses can have cut off weeks at the beginning and end of year. This is when your formula will get into trouble.
You would need to create a column in your date table that had each fortnight. This would be the key thing to sort. Then it would be a similar pattern in your formula to the example here
Hi Wounce, Unfortunately SELECTEDVALUE is currently unsupported in PowerPivot however there is an alternative which can be found in Microsoft documentation. An equivalent expression for SELECTEDVALUE(, ) is IF(HASONEVALUE(), VALUES(), ). You can often just use MAX instead of SELECTEDVALUE if you don't need to return an alternateResult when there is more than one value to choose from.
Be sure to check out the Enterprise DNA forum for any specific queries
Re ensuring week could be 52 or 53, I think this should work £ Costs PW = VAR CurrentWeek = SELECTEDVALUE('Date'[ISO Weeknumber]) VAR CurrentYear = SELECTEDVALUE('Date'[ISO Year]) // MaxWeekNumber finds the maximum week number - 52 or 53 VAR MaxWeekNumber = CALCULATE(MAX('Date'[ISO Weeknumber]), ALL('Date')) RETURN SUMX( FILTER(ALL('Date'), IF(CurrentWeek = 1 && MaxWeekNumber = 53, 'Date'[ISO Weeknumber] = MaxWeekNumber && 'Date'[ISO Year] = CurrentYear - 1, -- if week 1, and it's the final week of the year (53) then go back to the previous year and week 53 'Date'[ISO Weeknumber] = CurrentWeek - 1 && 'Date'[ISO Year] = CurrentYear) ), [£ Costs] )
***** Related Links *****
Compare Time Periods With Non Standard Date Tables - DAX & Power BI
th-cam.com/video/g_3eLaKgeEQ/w-d-xo.html
Placing Workday And Weekend Day Numbers Into The Date Table In Power BI
blog.enterprisedna.co/placing-workday-and-weekend-day-numbers-into-the-date-table-in-power-bi/
Calculate Consecutive Weekday & Weekend Days In Power BI Using DAX
blog.enterprisedna.co/calculate-consecutive-weekday-weekend-days-in-power-bi-using-dax/
***** Related Course Modules *****
Time Intelligence Calculations
portal.enterprisedna.co/p/time-intelligence-calculations
Advanced Data Transformations & Modeling
portal.enterprisedna.co/p/advanced-data-transformation-and-modeling
Mastering DAX Calculations
portal.enterprisedna.co/p/mastering-dax-calculations
***** Related Support Forums *****
Getting Last Week (shift specific) Data To Display In A Table
forum.enterprisedna.co/t/getting-last-week-shift-specific-data-to-display-in-a-table/3523
Same Period Last Year for Fiscal Week Nbr (weeks in sales table are non standard)
forum.enterprisedna.co/t/same-period-last-year-for-fiscal-week-nbr-weeks-in-sales-table-are-non-standard/240
Filter A Table By The Aggregation Of Its Self
forum.enterprisedna.co/t/filter-a-table-by-the-aggregation-of-its-self/4754
For more weekly sales queries to review see here…..
forum.enterprisedna.co/search?q=weekly%20sales
Thanks so much Enterprise DNA! You are a Legend in the PBI Community! Love you!
Hi Amit Pradhan, glad you appreciated the video. You can subscribe to our channel to see all our upcoming Power BI video tutorials.
Here’s the link: th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
@@EnterpriseDNA Sure just subscribed! Keep doing what you do! You rock
Brilliant Stuff. Thank you!! I Used this on Integer Sprint IDs instead of dates, works a charm
Thank you for sharing! This is just what I was looking for!
awesome solution Sam
Impressive this guy is a genius
Hi ManticOmar11, glad that you appreciated our content! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI and Power Platform video tutorials and announcements. Cheers!
Thanks for your video. I just used DATEADD from the beginning of your video. I also use a WeekEnding date instead of week numbers as they get cut off.
WeekEndingSat= Dates[Date] - WEEKDAY(Dates[Date], 1) + 7.
From here, I just use -7 DAY on the WeekEnding field, and ALL(Dates[Date]) for dates filtered by the slicer:
Hours_LastWeek := CALCULATE([Hours], DATEADD(Dates[WeekEndingSat], -7, DAY), ALL(Dates[Date]))
Brilliant
Thanks a lot for this formula. It was very useful to me albeit for a different functionality. Kudos!
Hi Anil, glad that you appreciated our content! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI and Power Platform video tutorials and announcements. Cheers!
Hi Sam, really a great video !! Looking forward to see more interesting topics on one of the best PowerBi / DAX channels on the web. Thumbs UP !!!
This video was so helpful thank you so much it is exactly what i needed for work!
Thank you so much for this Mr Sam. How can I get two separate weeks total on the card visuals
Thanks a lot! Great solution and video
Your videos are amazing and really helpful - thanks. My only suggestion would to use a pop filter/normalize the volume. Thanks.
Thank you so much. This is awesome.
Hi KKS Khin, glad you appreciated the video. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Cheers!
This is great!, thank you so much.
Thank you for sharing. This really works and super useful!
Hi Salvy Yu, glad you appreciated the video. We are so grateful to have had such positive feedback.
Amazing thinking
Hi Santosh, we're glad you appreciated the video! You can subscribe to our channel to see all our upcoming Power BI video tutorials. Cheers!
HI. I using this formula on two separate tables. One table contains a list of new applications and value I am counting are applications of type 'X'. The TW to LW count comparisons are bang on. But when I try the same calculation on another table that holds all the enrollments (calculation if a 'Distinct' row count, the LW number are off. They are always more that the TW value.
1:29 You can use it with using -7 in number of interval
Excellent, thank you
Great video!!
This is wonderful and I was able to get this to work for me. However, when I want to selected multiple weeks in my Slicer, it gives me a (Blank). Any pointers or another video you can refer me to?
Hey Ashton,
We're not too sure about how you're evaluating the results and in what context because in our PBIX file, the measure is working perfectly fine and yielding the results when multiple "Week & Year" are selected into the slicer.
It's a bit difficult to assess and give recommendation without looking at the data structure, model and working of the PBIX file for the reference. You can reach out to us on our Community Forum by providing a detailed description of your query along with relevant files for reference. Our members and team of Experts will be happy to help out!
If you haven't yet, do subscribe to our TH-cam channel and LinkedIn group to keep posted on Power BI, Power Platform, and the Microsoft stack updates.
Hoping you find this useful!
Cheers,
Enterprise DNA
forum.enterprisedna.co/
www.youtube.com/@EnterpriseDNA
www.linkedin.com/groups/12004506/
Great one sam... Liked it
Nice work
Hi Ubong, glad that you appreciated this video. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Cheers!
Hi Sam, this is amazing. Do you know how I might modify this DAX so that I can get TOTAL sales over the last 3 week period?
Hi!
Thank you for video.
Advise for newbies as I am))
I had an issue with CALCULATE formula with FILTER function.
I couldn't use any other column with the exception of "dates" column which was set in ALL function.
That happened due to the fact that I wrote not only the name of the table 'calendar' but also I specified parameter with a column name, so in the next steps formula didn't allow me to use something else except for dates.)
Thank you for sharing. If I want to do sales for previous 4 weeks that spill to previous year, how can I go about it?
Hi Olakunle, for help like this check out the Enterprise DNA Support Forum - forum.enterprisedna.co/
Hi, did you find any solution for this?
Hi great video could you advise how if more than 1 entry per week how to sum per week?
Thank you for your point of view on that! I tried (also tried some other solutions that I saw here on comments) and I guess I found a new way of doing the same, but with fewer lines:
PREVIOUS SW =
CALCULATE(SUMX(VALUES(Dates[Date]);CALCULATE(SUM(Sales[Sales]);DATEADD(Dates[Date];-7;DAY))))
Nice, I used this one, altered it to fit my model and worked fine - exactly what I needed. Thx!
might be a problem if you run this on the 2nd or 3rd day of the week, gives you wrong result. The one used here is better suited for day to day comparison.
That’s a rolling 7 day total
Hi Sam
First I would like to congratulate you on a number of very good videos then I have a question on how to create 10 weeks back
I guess that I should modify your code in 2 different places so both the week I check and the number of weeks has to go 10 weeks back as well?
Hopefully like I have done below?
Visitors -10w =
VAR CheckWeek =SELECTEDVALUE('Date'[APWeek])
VAR CheckYear =SELECTEDVALUE('Date'[APYearNo])
VAR MaxWeekNumber = CALCULATE (MAX('Date'[APWeek]))
RETURN
SUMX(
FILTER(ALL('Date');
IF(CheckWeek = 10;
'Date'[APWeek] = MaxWeekNumber && 'Date'[APYearNo] = CheckYear - 1;
'Date'[APWeek] = CheckWeek - 10 && 'Date'[APYearNo] = CheckYear));
[Visitors])
regards
Jørgen
Hi Jørgen,
Thanks for commenting.
Great to see you applying the patterns into your work. That’s the great thing about DAX so many of the patterns are reusable.
Your calculation makes sense if your data model is det up correctly.
Be sure to check out the Enterprise DNA forum for any specific queries.
forum.enterprisedna.co/
Regards
Enterprise DNA
Hi Jørgen, did you figure out how to do it?
Awesome video!. Is there a way to do this in Excel Power Pivot?, SELECTEDVALUE does not exist in PV. Thanks a lot!
Hello Juan Manuel,
thank you for posting your query onto our channel.
Well, our channel is mainly focused with the Power BI contents so we don't have much of an with the Excel Power Pivot functions.
For furthermore queries, you can also reach out to us onto our Community Forum where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference.
Hoping you find this useful.
Happy Learning!!!
forum.enterprisedna.co/
Thanks Sam, this is great ;-) I have another way I want to put this. How do I label my customer overdue orders so I can see how old they are. so I want to see the value of my current month orders, previous month 2 months ago, 3 months, 6 months, 1 year or longer. Them I want to put that in a list and see the individual amounts add up to the total. Can you help me with this? I figure I need a column with a calc, but I'm struggling to get the formula correct
Super Vid, Thanks
Glad it was helpful! Please dont forget to subscribe to our channel :)
I did exactly what you did and the table works, but why doesn't this data go nicely into a line chart, ex, this week by day vs previous week by day? Am I missing something?
Thank you for sharing this great video!! I've searched for days but finally got what I was looking for! One question: what to do with inconsistent weeks? for example my weeks are: 03/2021, 05/2021, 06/2021, 07/2021 etc. how can I make week 5 refer to week 3 value? Thank you!!!
Hello Rae Chaud,
thank you for posting your query onto our channel.
We're providing few of the links of the articles from our blog posts as well as links of the videos available onto our Enterprise DNA TH-cam channel pertaining to the topic where we've inconsistent calendar or date table in our scenario and how to achieve the results.
For furthermore queries, you can also reach out to us onto our Community Forum where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference as well.
Hoping you find this useful and meets your requirements that you've been looking for.
Happy Learning!!!
blog.enterprisedna.co/compare-time-periods-with-non-standard-date-tables-dax-and-power-bi/
blog.enterprisedna.co/common-time-intelligence-patterns-used-in-power-bi/
blog.enterprisedna.co/year-to-date-sales-for-power-bi-custom-calendar-tables/
blog.enterprisedna.co/calculating-month-on-month-change-custom-calendars-power-bi-and-dax/
blog.enterprisedna.co/the-dateadd-function-the-best-and-most-versatile-time-intelligence-function-in-power-bi/
community.powerbi.com/t5/Community-Blog/Unique-Time-Intelligence-Analysis-Examples-for-Power-BI/ba-p/1085176
forum.enterprisedna.co/t/non-standard-time-intelligence/10452
th-cam.com/video/g_3eLaKgeEQ/w-d-xo.html
th-cam.com/video/aODroF37pq0/w-d-xo.html
forum.enterprisedna.co/
Sam - it was much needed video. I hit with the same issue and solved myself recently.. however I couldn't implement YTD,Mtd..etc on top of last week sales measure.. would be great if you could extend this series to it
Watch out for tomorrows video...it shows you how to solve this
Was this video uploaded
thank you for this. I will definitely have a stab at it.
I have a situation where I have different products and our week in KSA starts on Sunday and ends on Thursday so weekend is on Fri and Sat. How would I measure daily changes?? would appreciate it if you could help me out in this. I would put that formula within the model if possible when transforming the data from my excel source file so that I can focus later on only on visualising and analysing
Hello Ahmad Jaber,
thank you for posting your query onto our channel.
We're providing a link below from our Community Forum where similar query was asked by a member about how to start the week on "Saturday" whereas in your case it's on "Sunday". Just follow the steps as showcased into the link and you'll be able to achieve the desired results.
For furthermore queries, you can also reach out to us onto our Community Forum where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference as well.
Hoping you find this useful and meets your requirements that you've been looking for.
Also make sure that you're subscribed to our TH-cam channel so that you don't miss out on any updates pertaining to the Power BI.
Happy Learning!!!
forum.enterprisedna.co/
th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
forum.enterprisedna.co/t/date-table-fiscal-week-start-saturday/14909/7
Congratulations. The PW is the besr :D
Hi Sam,
Thank you for the valuable information, but unfortunately when I implement the same steps to my model I received the following error "DAX comparison operation do not support comparing values of Type text with values of Type integer" and I'm sure that non of my values are text..!! any help or ideas in this strange issue. Regards
Hi Abdullah,
Thank you for posting your query onto our channel. We really appreciate you taking your valuable time to post it.
Well it's always a little bit difficult to judge and provide the results without looking at the data structure, model and working of the PBIX file for the reference. And so we're not in a position to judge or provide comment on anything.
Therefore we encourage you to please write back to us onto our Community Forum by providing the proper description of the problem that you're facing along with the PBIX file for the reference as well as the mock-up results that you're trying to achieve where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference.
The only thing is, there's has to be some sort of text value which is getting compared with the integer and so it gives this kind of error.
Hoping you find this useful. Also, make sure that you're subscribed to our TH-cam channel so that you don't miss out on any updates pertaining to the Power BI. You can also join our Power BI group on LinkedIn to receive latest updates on Power BI. Below are the links provided for the reference purposes.
Cheers,
Enterprise DNA
forum.enterprisedna.co/
th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
www.linkedin.com/groups/12004506/
@Sam Mckay, Thank you so much for this video, however, i do have few questions that Im not able to accomplish.
1) Here in this video, You are showing year and week wise in the Rows but how can we show the previous week sales bases on dimension like City, Product etc.
2) I work Excel 2016, and I do not see "selected value" Dax function. How can I solve this. Please help
Hello Mohammad,
Thank You for posting your query onto our channel.
Well for the first question, you cannot simply show the previous week sales or be it any number evaluated based on time intelligence calculations just on the basis of dimensions like City, Product, etc. The reason being is, they'll only be able to evaluate the results just for those dimensions and that too for the entire period unless and until date context is transferred or provided into those visuals either by dragging date field directly into those visuals alongwith the City or Product dimension or any date dimension is selected in the form of slicer. You got to provide date context in any form in order to evaluate and achieve this results.
As pertaining to the second question, you can raise this query onto the Excel Community forum where excel experts will be able to assist you.
For furthermore queries, you can also reach out to us onto our Community Forum where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference as well.
Hoping you find this useful and meets your requirements that you've been looking for. Also make sure that you're subscribed to our TH-cam channel so that you don't miss out on any updates pertaining to the Power BI.
Happy Learning!
Thanks and Warm Regards,
Enterprise DNA
forum.enterprisedna.co/
th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
@@EnterpriseDNA Thank you so much for responding to my query and for the insights.
Hi Sam, Superb video and you're making the people to understand in a best way with good logic. I am a die Hard fan of your DAX logic and almost follow your foot steps.
I just have a quick question and seeking help for the same from you. I have a sales data set on hourly basis for four year and need to understand how do i calculate the previous year's date and hours sales calculation. I am using the Previous year sales data to compare with current year sales data. I have working on E-Commerce website analytics and using power bi for building dashboard and Reports. Your kind help with solution is appreciated.
First you will want to break out the time and dates into separate column usually. Then use time intelligence function to calculate what you require. Give this a go first and I think you'll see the end game clearer this way.
running into an issue with sales PW - is there a video on how you created total sales field??
For help like this check out the Enterprise DNA Support Forum - forum.enterprisedna.co/search?q=total%20sales%20field
Hi, Thank You for sharing this video. It is really helpful. I would like to know how I can calculate the difference sales between two dates? I have tried the function which you gave mentioned in this video at 0.54 sec. I am not getting the result for monday (5th Aug). In my data I have values till 2nd aug ($109,111) and after that 5th Aug($98,655). I want the difference between these dates as well. Can you please help me reagrding this?
Hi Sam, Great Video, very clear, it works great for me, but when I incorporated the categories (food, beverages), the previous week expression showed the sum of hole week instead of the sales of that particular category previous week.... what I am missing out? regards! Matias
You likely need to check that your relationships are set up correctly in your model
Hi, were you able to figure it out by Category?
Thanks for your reply, it didn't work, I have only 1 table by the way...
Finally after a lot of struggle found this solution of if you keep categories or anything into filter. Instead of using calculate(sales,filter(all(table1)) use calculate(sales,filter(allselected(table1))) this would give sales value with respect to filter you place.
Another way is you can still use the calculates(sales,filter(all(table1),table1(category)=min(table1(category))&&quarterno=min(quarterno)&&yearno=min(yearno))
Hi , is there a video on 52 weeks. If not please
Hello SUNIL NALLAGONDA, thank you for posting your query onto our channel.
Well same concept is applicable for week 52 as well as week 53. If you scroll through till the end you'll witness that for the year 2017 there were 52 weeks and the results are calculated accordingly. Also we're providing a link below that contains several posts pertaining to the M Code which shows the results according to the different calendars applicable in different scenarios.
For furthermore queries, you can also reach out to us onto our Community Forum where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference as well.
Hoping you find this useful and meets your requirements that you've been looking for.
Happy Learning!!!
forum.enterprisedna.co/c/M-Code-Showcase/38
forum.enterprisedna.co/t/cumulative-weekly-sales-measure/437
blog.enterprisedna.co/calculate-previous-weeks-sales-advanced-dax-in-power-bi/
forum.enterprisedna.co/
Hi - this works really well except when there are no sales for a particular week but there are for the previous week. In that case both the previous week and the current will show blank when the previous week should have a value. This is because the SELECTEDVALUE is not returning anything for the current week (VAR CurrentWeek = SELECTEDVALUE(dimDate[WeekNumber]). Is there a way around this?
Hi Robert, for help like this check out the Enterprise DNA Support Forum - forum.enterprisedna.co/search?q=Week%20Sales
Hi Sam. Thanks for your super-informative video that was thoroughly well-explained. I'd like to know how to compare this year's sales in WeekNumber vs. the same WeekNumber last year. E.g. week 3's sales in 2021 vs. week 3's sales in 2020, and so on. I would deeply appreciate it if you would kindly assist, please.
The idea would be to reflect the results in 2 views: 1) like in your video with the column charts, and 2) as a line chart where the full year of 2020 is visible and 2021 year-to-date is visible as a separate line.
I hope to hear from you on this. Thanks
Hello Andrew Newey,
thank you for posting your query onto our channel.
Well, after calculating the Weekly Sales you can compare it with the last year by using the "DATEADD()", "SAMEPERIODLASTYEAR()" or "PARALLELPERIOD()" functions. It's always a little bit difficult to judge and provide the results without looking at the data structure, model and working of the PBIX file for the reference. But still you can go through some of the videos available pertaining to this concept which are also available onto our Enterprise DNA TH-cam channel.
Also we encourage you to please write back to us onto our Community Forum by providing the proper description of the problem that you're facing along with the PBIX file for the reference as well as the mock-up results that you're trying to achieve where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference.
Hoping you find this useful.
Happy Learning!!!
forum.enterprisedna.co/
Hi Sam,
I have tested and used this formula and it works great.
I have a slight problem when using the same logic but instead calculating for Last Month instead of last week. it basically gives me 0 for every January occurrence.
Could you pinpoint to me as to what i might be missing.
This is my logic below
HardwareSalesLM =
var CurrentYear = SELECTEDVALUE(DimDateCalendar[Year])
var CurrentMonth = SELECTEDVALUE(DimDateCalendar[MonthNum])
var MaxMonthNumber = CALCULATE(Max(DimDateCalendar[Month]);ALL(DimDateCalendar))
Return
IF(SUMX(
FILTER(ALL(DimDateCalendar);
If (CurrentMonth = 1;
DimDateCalendar[MonthNum] = MaxMonthNumber && DimDateCalendar[Year] = CurrentYear -1;
DimDateCalendar[MonthNum] = CurrentMonth - 1 && DimDateCalendar[Year] = CurrentYear));
[TotalHardwareSales]) = BLANK();0 ;
SUMX(
FILTER(ALL(DimDateCalendar);
If (CurrentMonth = 1;
DimDateCalendar[MonthNum] = MaxMonthNumber && DimDateCalendar[Year] = CurrentYear -1;
DimDateCalendar[MonthNum] = CurrentMonth - 1 && DimDateCalendar[Year] = CurrentYear));
[TotalHardwareSales]))
Thanks
In case week numbers are not in sequence order ,how do we achieve previous week sale data pls help sir.. for example week numbers in my table are below and I need to calculate sales for previous week based on week number selected on slicer.
Week number
10
13
14
16
20
28
Hi sam, is the second part also possible with calculate instead of sumx??
Yep for sure. You can get same results using either in a number of situations
I liked it
Great video Sam! What if the metric you are comparing week over week? i tried using AverageX, but this is not working
i figured it out. :-) i needed to add DIVIDE to my RETURN statement
I have a situation like this, for calculating MoM and WoW, I need to consider same period in the previous year. Let's say if I want to return MoM for August 2021, then calculation would be like (Aug_2021 sales - Aug_2020 sales)/Aug_2020 sales * 100. Like this it should be dynamic for MoM and WoW and in the view I need to display like Last 12 months for MoM % growth, and Last 13 months for WoW % growth. I achieved the calculation but not able to restrict last 12 months/13 weeks. Any help on this would be helpful.
Note: we are not supposed to use calendar table as per our requirement. So considering sales_date from data itself and I achieved WoW and MoM based on month_number and Week_Number. And based on maximum sales_date, we need to display last 12 months or Last 13 weeks.
Hello Govardhan Bola,
Thank you for posting your query onto our channel.
Firstly, "Date/Calendar Table" is a mandatory requirement in order to have a time-intelligence results and without that you won't be able to achieve the desired results. Below is the link provided for reference about why date table is important to have into the data model.
Also it's always a little bit difficult to judge and provide the results without looking at the data structure, model and working of the PBIX file for the reference.
And therefore we encourage you to please write back to us onto our Community Forum by providing the proper description of the problem that you're facing along with the PBIX file for the reference as well as the mock-up results that you're trying to achieve where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference.
Hoping you find this useful and helpful. Also, make sure that you're subscribed to our TH-cam channel so that you don't miss out on any updates pertaining to the Power BI.
Cheers,
Enterprise DNA
forum.enterprisedna.co/
th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
forum.enterprisedna.co/t/basic-date-table-power-query-m-function/19055
forum.enterprisedna.co/t/extended-date-table-power-query-m-function/6390
Amazingly well executed tutorial. I wonder if I can still add a question here. How can one modify the formula to fetch the sales in similar logic "Two weeks ago". I tried to simply changing it to Dates[ISO WeekNumber] = CurrentWeek - 2 && Dates[Year] = CurrentYear ) ), -2 , but then it wont be able to succesfully fetch the number from last year, but otherwise it works
Hello B0ge,
Thank you for posting your query onto our channel.
Well, this is where the situation gets trickier because just by changing the logic from " - 1" to " - 2" will not actually derive the correct results in that case you'll have to modify the context into the formula to achieve the results. Below is the measure provided for the reference that will get you correct results for Previous Two Week Sales -
Sales P2W =
VAR CurrentWeek = SELECTEDVALUE( Dates[Week Number] )
VAR CurrentYear = SELECTEDVALUE( Dates[Year] )
VAR MaxWeekNumber = CALCULATE( MAX( Dates[Week Number] ), ALL( Dates ) )
RETURN
SUMX(
FILTER( ALL( Dates ),
IF( CurrentWeek = 2,
Dates[Week Number] = MaxWeekNumber && Dates[Year] = CurrentYear - 1,
IF( CurrentWeek = 1,
Dates[Week Number] = MaxWeekNumber - 1 && Dates[Year] = CurrentYear - 1,
Dates[Week Number] = CurrentWeek - 2 && Dates[Year] = CurrentYear ) ) ),
[Total Sales] )
For furthermore queries, you can also reach out to us onto our Community Forum where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference as well.
Hoping you find this useful and meets your requirements that you've been looking for. Also, make sure that you're subscribed to our TH-cam channel so that you don't miss out on any updates pertaining to the Power BI.
Cheers,
Enterprise DNA
forum.enterprisedna.co/
th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
Hi, great video! I have just one question, maybe you could help me out a bit. I have daily data from 2014 onwards, and your measure works great, except in one case - when I have for example W1 of 2019, your measure for previous week is not returning anything because the last week in 2018 was W52. I think the problem is that the MAX Week Number is 53, and not 52, so when the previous year is standard and has 52 weeks, then your solution will not return anything for the first week for the current year. In the dataset you presented in the video, for the first week of 2018 measure should return blank since 2017 had 52 weeks. Can you help me find a solution for this problem? Maybe something is wrong with my data or my calendar?
I have just recently learning DAX, and I am amazed how extremely cumbersome this it when compare to excel formula (where you can just use a helper column and sumifs), or am I missing something?
Hello kerry r,
thank you for posting your query onto our channel.
Well this is the reason why it's always said "DAX is simple but not easy". We sincerely don't recommend to use of the helper columns where the the work can be simply done using the measures. Because more the number of the columns into the data, the larger will be the size of the data more.
We provide the free beginner's course of the "Power BI and DAX". We encourage you please go through those free courses in order to strengthen the fundamentals of Power BI and than you can also upgrade to the premium courses that we offer at a subsidized rates which offers great deal of benefits. Below is the link of the beginners course provided for the reference.
For furthermore queries, you can also reach out to us onto our Community Forum where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference as well.
Hoping you find this useful and helps you in your learning!!!
Happy Learning!!!
portal.enterprisedna.co/courses/enrolled/157239
portal.enterprisedna.co/courses/enrolled/195681
forum.enterprisedna.co/
Hi Sam, why don't we use -7 days inside dateadd?
You could potentially do that also. I think that setting it up like this is probably a better solution to align to a custom date table. But if -7 days does the trick also, I don't think you can go wrong. Chrs
-7 days works fine for all dates except the first two dates of each month..it'd be great if you can find some time to look into it
I have a field on my date table for week ending: _dcWeekEndingSat = _dtDateFilter[Date Filter] - WEEKDAY(_dtDateFilter[Date Filter], 1) + 7.
From here, I just use -7 DAY on the WeekEnding field, and ALL(_dtDateFilter[Date Filter]) for dates filtered by the slicer:
_dm_Vol_Handling_Total_Hours_LastWeek = CALCULATE([_dm_ToT_Handling_Total_Hours], DATEADD(_dtDateFilter[_dcWeekEndingSat], -7, DAY), ALL(_dtDateFilter[Date filter]))
Superb explanation!
I notice though, both "Prior week Sales" measures do not have a Grand Total. Would you mind explaining why the Power BI (or DAX) does not generating them? If it's possible to generate them, would you mind sharing how to do so ? I appreciate it!
For help like this check out the Enterprise DNA Support Forum - forum.enterprisedna.co/
@@EnterpriseDNA,
Thank you. I will check it out and test it.
i am able to get the data but if I add filter to a specific week number, I do not get previous weeks data - this is being filtered as well
Hello Vijay,
Thank You for posting your query onto our channel.
Well it's always a little bit difficult to judge and provide the results without looking at the data structure, model and working of the PBIX file for the reference.
And therefore we encourage you to please write back to us onto our Community Forum by providing the proper description of the problem that you're facing along with the PBIX file for the reference as well as the mock-up results that you're trying to achieve where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference.
Hoping you find this useful. Also make sure that you're subscribed to our TH-cam channel so that you don't miss out on any updates pertaining to the Power BI.
Happy Learning!
Thanks and Warm Regards,
Enterprise DNA
forum.enterprisedna.co/
th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
What's the small adjustment to fix the issue of 52 weeks some years and 53 in others?
Hello Sam, good talking to you again, the second part is giving an error regarding the data types, what could be reason??? I am subscriber from your channel and I do have access to all your TV resources. Instead of using totals sales I am using the total claims.
Hard to say, is the example model erroring out or just yours? Have you checked out the column data type to match them to the example?
Hello Sam, Great Video...I dont understand so well what you mean witn a potencial error with the week 52 and 53....are you goint to show that case?...Thnak you ...this is a great channel!!
All I mean is that you could have 52 or 53 weeks. It would be a small adjustment to the formula.
@@EnterpriseDNA thank you for the video
Can you please tell me what is this adjustment please
thank you in advance
Hi,
Great video! Thanks a lot.
But I have an issue. The measure "Sales PW" goes well, except several weeks in the table. I have 2017-2019 data. All is ok except year- 2018, wk 2-11. At that time 2018/19, wk 2-11, is ok. Can you give some hint how to solve it. Thanks for your help in advance. :-)
Hi,
I'm trying to use this formula in my calculations, but it gives me the error of:
A single value for column 'WeekNumber" in table 'Date' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum, to get a single result.
What could possibly be the reason for this?
Thanks beforehand for responding, I'm loving your videos!
After adding measures, Weekly Diff & Weekly Diff %, could Sales PW filtering be used to get previous week's Weekly Diff % in order to get the Weekly Growth (Expanding & Contracting) by using Weekly Diff % in the SUMX?
Yes definitely, all you have to do is branch out into those new measures. Use the measures you have inside another measure to get those results.
Hi, i need help i am working with power pivot on excel 2016, the function selected value is not available, do you know if there is any substitute of the function in power pivot for excel 2016?
IF( HASONEVALUE( column name ), VALUES( column name )
This is how you would do it.
Hi,
I have a problem when trying to calculate weekly sale of a particular customer, then compared with previous week.
So I have Name, Date, Sale. So I want to calculate how much the sale of particular customer, then compared to previous week. Could you provide some inside? Thank you.
Hello baonguyen1705,
thank you for posting your query onto our channel.
Well it's always a little bit difficult to judge and provide the results without looking at the data structure, model and working of the PBIX file for the reference.
And therefore we encourage you to please write back to us onto our Community Forum by providing the proper description of the problem that you're facing along with the PBIX file for the reference as well as the mock-up results that you're trying to achieve where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference.
Hoping you find this helpful.
Happy Learning!!!
forum.enterprisedna.co/
Thank you so much..I wanted this but when I am trying to get previous week value for each store, I am getting the average value of all stores in the previous week instead of store by store value.
Can you please help me with this?
For example: I have
Store Week44-Value Week45-Value
A 20%. 30%
B. 40%. 40%
If 45 is my current week, The previous week value for store A will be 20% and that of store B will be 40%...But I am getting same value for store A and store B 30% which is the average of store A and Store B in week 44
Please help me
Thanks
Great! How is it possible to put 2 equals signs inside of an IF statement ? In the true and false part...don’t see the logic. Looks like your assigning a variable?
Great ... Comparing Weeks not that easy, especially if you have years with 52 and 53 weeks. Would be great if you could do some more calculations based on calendar weeks. Thanks for this video.
Have done a few now, maybe in time will have a look at some other scenarios
Hi Sam, i want to find out the sum of last 4 weeks for every week what can i do.. could you please help thanks..
All that would take is some slight adjustment to this exact formula pattern described in the video. Chrs
How would I capture the average and std deviation of the previous 53 weeks?
Hi Chris A,
You could quite easily extend this pattern and for average or std dev.
The key is ensuring you have a good date table.
You then need to change the context of your calculation to evaluate for the 53 weeks you are interested in. Remember there is no Week concept in time intelligence within power bi you therefore need to do the conversion via days.
Be sure to checkout the Enterprise DNA forum for any specific queries.
forum.enterprisedna.co/
Regards
Enterprise DNA
what is the format for the year week columns ? When I do my graph it is sorting the amounts by 20171 201710 201711 and so on.
Hi Karen, check out the Enterprise DNA support forum thread about 'FY Week Number Column' - forum.enterprisedna.co/t/fy-week-number-column/2059
Hello, I have 3 years of data, the first year have 52 weeks of payments, the second year has 53, so when I applied the logic you explained here is using 53 as the maxweeknumber. Could you give us an example in how to overcome that issue?
Thanks
Hi Karen, for help like this check out the Enterprise DNA Support Forum - forum.enterprisedna.co/search?q=maxweeknumber
Did you ever make a video with 52 weeks some years instead of 53?
I found a way to make the maxweeks work with
var maxweeksprioryear = CALCULATE(CALCULATE(MAX('Calendar'[WeekNumber]),'Calendar'[WeekYear]=currentyear-1),ALL('Calendar'))
@@alexandermilland6674 thank you so much!!! You saved my day with this !!! Works EXCELLENT on years with 52 or 53 weeks.
Could you please share the pbix file
what about calculating day on day change but when there is not activity on saturday and sunday. So each week ends with friday and the next row is monday unless there is a public holiday. So how to do day on day % change
You would just need to FILTER out those weekend dates in you initial calculation.
I had a category as a condition too. So I did 2 calculated columns - one for a sequence of date by the category and one for the value for the last sequence order using FILTER, ALLEXCEPT and EARLIER -1. And then a calculated measure to do a % change between the 2 columns. As a calculated measure EARLIER was not picking up column names...Unless I am missing a trick here..
I just used: Net Sales 7 Days Prior = CALCULATE([Sum Net Transactional Sales Ex Discount],DATEADD('Date'[full_date],-7,DAY)) -- Same Result
Yes that's fine, but not necessarily all the time. Some date calendars within businesses can have cut off weeks at the beginning and end of year. This is when your formula will get into trouble.
How could adjust to fornighlty instead of weekly ?
You would need to create a column in your date table that had each fortnight. This would be the key thing to sort. Then it would be a similar pattern in your formula to the example here
Please share .pbix file of this tutorial
Thanks a lot! Great explanation but it would be better to improve your sound quality.
I am unable to replicate this as a DAX Measure in Excel due to there not being a 'SELECTEDVALUE' function available. :(
Hi Wounce,
Unfortunately SELECTEDVALUE is currently unsupported in PowerPivot however there is an alternative which can be found in Microsoft documentation.
An equivalent expression for SELECTEDVALUE(, ) is IF(HASONEVALUE(), VALUES(), ).
You can often just use MAX instead of SELECTEDVALUE if you don't need to return an alternateResult when there is more than one value to choose from.
Be sure to check out the Enterprise DNA forum for any specific queries
Regards,
Enterprise DNA
For some strange reason I still get the blanks when it comes to Week 1. I have used both Dax formulas and get the same result.
Should work. I'm not sure without seeing the entire model.
Re ensuring week could be 52 or 53, I think this should work
£ Costs PW =
VAR CurrentWeek = SELECTEDVALUE('Date'[ISO Weeknumber])
VAR CurrentYear = SELECTEDVALUE('Date'[ISO Year])
// MaxWeekNumber finds the maximum week number - 52 or 53
VAR MaxWeekNumber = CALCULATE(MAX('Date'[ISO Weeknumber]), ALL('Date'))
RETURN
SUMX(
FILTER(ALL('Date'),
IF(CurrentWeek = 1 && MaxWeekNumber = 53,
'Date'[ISO Weeknumber] = MaxWeekNumber && 'Date'[ISO Year] = CurrentYear - 1, -- if week 1, and it's the final week of the year (53) then go back to the previous year and week 53
'Date'[ISO Weeknumber] = CurrentWeek - 1 && 'Date'[ISO Year] = CurrentYear)
),
[£ Costs]
)
Week 52 vs Week 53 Potential Solution:
Previous Week =
VAR _CurrentWeek = SELECTEDVALUE('Date DIM'[WEEK_NO_ISO])
VAR _CurrentYear = SELECTEDVALUE('Date DIM'[YEAR_NO_ISO])
VAR _PreviousYear = _CurrentYear - 1
VAR _MaxWeekPY = CALCULATE(
MAX('Date DIM'[WEEK_NO_ISO]),
FILTER(
ALL('Date DIM'),
'Date DIM'[YEAR_NO_ISO] = _PreviousYear
)
)
VAR _TargetWeek = IF ( _CurrentWeek = 1, _MaxWeekPY, _CurrentWeek - 1 )
VAR _TargetYear = IF ( _CurrentWeek = 1, _PreviousYear, _CurrentYear )
VAR _Result =
SUMX(
FILTER(
ALL('Date DIM'),
'Date DIM'[WEEK_NO_ISO] = _TargetWeek &&
'Date DIM'[YEAR_NO_ISO] = _TargetYear
),
[# OB Entries]
)
RETURN
_Result