This is great. What about something where if you select a given date, it compares the totals for that day to the average of the prior X number of that same day of the week? For instance, if I select X date, and that is a Monday, it will compare the Monday I selected to the average Y Mondays prior to the one I selected.
I think you are missing showing some steps within this video. Such as how to make the formulas dropdown. Because I cannot seem to get it to work with the workweek
How to take the sales change wow on only one particular day of the week once a week sales calculated and week over week change....any way to select particular date on dataset and show the change...please help
Hi, your video have been very helpful for me. I'm looking to so something similar to what you did in this video, but with the option of selecting the week via slicer. As, if i select week 23, i would like the table to show the data for week 23 and 22. if i select week 10, the table should show data for weeks 10 and 9, and so on. However i have been struggling to achieve the result i need...
Exactly what Im looking for bit power BI is beyond me. Can this be done in excel pivot table?….. a this week to last week side by side then a variance?
Avi, Thanks for your videos, they have been very helpful on my power bi journey. My question is why does the ultimate calendar table have 53 weeks of year instead of 52?
Hi avi, i am trying to create a dynamic measure like based on th selected date data should display in matrix level but if the week selection is in between its not displaying any data.can you please help me on the dax.i have year week,month, value columns in table A,slicer month column coming from table B
Hi Avi, Thanks for your helpful video. My question is: I need to show data by reporting months which start from 24th of their last month to 23th of these months (e.g: The duration of December,2022 in my report will span from 24th November,2022 to 23th December, 2022) and by my reporting weeks which start from the last Thursday to its current Wednesday. How does my report always show current REPORTING WEEK/MONTH vs. prior REPORTING WEEK/ MONTH? And is there any way to calculate the portion of top 10 product code by sales automatically? (because my product code lists include many codes so create each measure to calculate each of their portion is not the best choice). Looking forward to hearing from you!
I have a situation. My "current week" is actually the last week of deliveries. So, my question is, how can I set up my current week to always be the last week of deliveries (fact table)? I would appreciate your help.
Hey @FELIPPEVIANNA, That is a great scenario. And I think I can help you with that. Can you send me a message on LinkedIn www.linkedin.com/in/PowerBIPro We may need some back and forth to solve this for you. And I check LinkedIn a lot more than I check RUclips comments here. Power On! / Avi
Hi I want to create a rolling week number x axis with the current week in the center , in the left the 26 weeks before and in the right the 26 weeks after. Can you help me ?
Hey Yamina, Would love to help. Please check out www.learnpowerbi.com/article/how-to-ask-good-power-bi-questions-and-get-them-answered And email me your question at avi@avising.com You can also join my Live Power BI Q&A Call. See www.LearnPowerBI.com/question Power On! / Avi
If you download his calender table, you'll see it in the queries when you click transform data how he made it. If you're lazy like me, here it is but doesn't make sense unless you see the other queries tbh: = Table.AddColumn(#"----WeekSequenceNum Complete----", "CurWeekOffset", each [WeekSequenceNum] - Current_WeekSequenceNum, Int64.Type)
hi, I am trying to calculate a YTD value by selecting in the slicer the week number. so if I select the week 10, then the returned values should be "value week 1 + value week 2... + value week 10". I can only do it by using TOTALYTD and selecting the week date instead of the week number. do you have another solution for it? thanks!
@@PowerBIPro Thank you for replying Avi. I’m getting this error “Expression.Error: The name ‘WEEKNUM’ wasn’t recognized. Make sure it’s spelled correctly”
Hey - I have a unrelated question - I have been trying for a number of weeks to incorporate a chiclet sliver to my report. I wanted to create a slicer with headshots of my team members (just thought it would be cooler then a boring slicer) but for some reason I cannot get all 4 images to work. I have saved the image links to our company sharepoint and into our team so the links should be accessible and also categorized the links to "image url". But still no luck. Any help would be amazing!!!!! Thank you
To change the first day of the week to Monday in Power BI, you can follow these steps: Click on "File" in the top left corner of the Power BI Desktop window. Select "Options and settings" from the dropdown menu. Click on "Options". In the "Options" dialog box, select "Regional settings" from the left-hand menu. In the "Regional settings" section, look for the "Week starts on" option and select "Monday" from the dropdown menu. Click on "OK" to save the changes. Once you have made this change, all your date and time functions, including WEEKNUM and other functions that rely on the week starting on a specific day, will use Monday as the first day of the week.
Wait, am I the only one seeing that “sales in prior week” is not actually in the “sales in current week” column as it should be? None of the digits in both column are not the same.
I used this for work today, thank you so much!
Wonderful!
Looking for same concept, thank you Avi making my life easy
Thank you Avi. This is very helpful. The Calculate function is not working for me. No drop downs
This is great. What about something where if you select a given date, it compares the totals for that day to the average of the prior X number of that same day of the week? For instance, if I select X date, and that is a Monday, it will compare the Monday I selected to the average Y Mondays prior to the one I selected.
Avi, how did you set up the underlying table and header for curr vs pre week's to show weekdays and the header.
I think you are missing showing some steps within this video. Such as how to make the formulas dropdown. Because I cannot seem to get it to work with the workweek
Great video, really useful, what would have been challenging, add % per day current and prior week
How to take the sales change wow on only one particular day of the week once a week sales calculated and week over week change....any way to select particular date on dataset and show the change...please help
Hi,
your video have been very helpful for me.
I'm looking to so something similar to what you did in this video, but with the option of selecting the week via slicer.
As, if i select week 23, i would like the table to show the data for week 23 and 22. if i select week 10, the table should show data for weeks 10 and 9, and so on.
However i have been struggling to achieve the result i need...
Hey Andrea, send me a message on LinkedIn and I would help you the best I can. www.linkedin.com/in/PowerBIPro
Power On! / Avi
Hi, Thank you for the videos, hou did you add the legend on top showing the current week Sun - Sat? is it a matrix table?
Exactly what Im looking for bit power BI is beyond me. Can this be done in excel pivot table?….. a this week to last week side by side then a variance?
Does that work with summarized data? If I wanted to slice by vendor or market sales deltas?
Avi, Thanks for your videos, they have been very helpful on my power bi journey.
My question is why does the ultimate calendar table have 53 weeks of year instead of 52?
Hi avi, i am trying to create a dynamic measure like based on th selected date data should display in matrix level but if the week selection is in between its not displaying any data.can you please help me on the dax.i have year week,month, value columns in table A,slicer month column coming from table B
How to set up this offset? I have normal calendar table and there i have week number.
Must awaited as I have done earlier
Hi Avi, Thanks for your helpful video. My question is: I need to show data by reporting months which start from 24th of their last month to 23th of these months (e.g: The duration of December,2022 in my report will span from 24th November,2022 to 23th December, 2022) and by my reporting weeks which start from the last Thursday to its current Wednesday. How does my report always show current REPORTING WEEK/MONTH vs. prior REPORTING WEEK/ MONTH? And is there any way to calculate the portion of top 10 product code by sales automatically? (because my product code lists include many codes so create each measure to calculate each of their portion is not the best choice). Looking forward to hearing from you!
I got the calender table from you but how do I change the start of the week? I want my week to start on Monday and end on Sunday.
I have a situation. My "current week" is actually the last week of deliveries. So, my question is, how can I set up my current week to always be the last week of deliveries (fact table)? I would appreciate your help.
Hey @FELIPPEVIANNA,
That is a great scenario. And I think I can help you with that. Can you send me a message on LinkedIn www.linkedin.com/in/PowerBIPro
We may need some back and forth to solve this for you. And I check LinkedIn a lot more than I check RUclips comments here.
Power On! / Avi
Hi I want to create a rolling week number x axis with the current week in the center , in the left the 26 weeks before and in the right the 26 weeks after. Can you help me ?
Hey Yamina,
Would love to help. Please check out
www.learnpowerbi.com/article/how-to-ask-good-power-bi-questions-and-get-them-answered
And email me your question at avi@avising.com
You can also join my Live Power BI Q&A Call.
See www.LearnPowerBI.com/question
Power On! / Avi
Hi Avi,
Could you please help with how to create current week offset flags in a report?
Dear Avi,
Is CurWeekOffSet a calculated column? I think, it is a dynamic value based on current date, isn't it?
How is the formula?
If you download his calender table, you'll see it in the queries when you click transform data how he made it. If you're lazy like me, here it is but doesn't make sense unless you see the other queries tbh:
= Table.AddColumn(#"----WeekSequenceNum Complete----", "CurWeekOffset", each [WeekSequenceNum] - Current_WeekSequenceNum, Int64.Type)
@@Harpreet06 ah thank you...
hi, I am trying to calculate a YTD value by selecting in the slicer the week number. so if I select the week 10, then the returned values should be "value week 1 + value week 2... + value week 10". I can only do it by using TOTALYTD and selecting the week date instead of the week number. do you have another solution for it? thanks!
Values aren't reflecting in sales current week and sales prior week measures in my .pbix file. Kindly help me understand why is this issue occuring
I’m having trouble changing the week start day. What if I want the weeks to be Mon - Sun?
Hi Abi, pls. how do you tackle ISOWEEKNUM? This exists in excel but PBi only has WEEKNUM function.
In Power BI, you can calculate the ISO week number by using the following formula: ISOWeekNum = WEEKNUM([Date], 21)
@@PowerBIPro Thank you for replying Avi. I’m getting this error “Expression.Error: The name ‘WEEKNUM’ wasn’t recognized. Make sure it’s spelled correctly”
Works now thanks. Instead of typing the formula in the Data view, I was doing that in the Edit query.
How you calclaut the sales?
Hey - I have a unrelated question - I have been trying for a number of weeks to incorporate a chiclet sliver to my report. I wanted to create a slicer with headshots of my team members (just thought it would be cooler then a boring slicer) but for some reason I cannot get all 4 images to work. I have saved the image links to our company sharepoint and into our team so the links should be accessible and also categorized the links to "image url". But still no luck. Any help would be amazing!!!!! Thank you
I am not getting any value after calculating the current week sales its showing 00. How to solve this one.
Awesome stuff
How can always calculate my weeks from yesterday, not including todays data
how can i change the first day of the week as Monday??
To change the first day of the week to Monday in Power BI, you can follow these steps:
Click on "File" in the top left corner of the Power BI Desktop window.
Select "Options and settings" from the dropdown menu.
Click on "Options".
In the "Options" dialog box, select "Regional settings" from the left-hand menu.
In the "Regional settings" section, look for the "Week starts on" option and select "Monday" from the dropdown menu.
Click on "OK" to save the changes.
Once you have made this change, all your date and time functions, including WEEKNUM and other functions that rely on the week starting on a specific day, will use Monday as the first day of the week.
@@PowerBIPro TY SO MUCH
Wait, am I the only one seeing that “sales in prior week” is not actually in the “sales in current week” column as it should be? None of the digits in both column are not the same.