This is everything I needed! Thank you! For your reference, my default Canadian setting showed day of week as Saturday as 6 and Sunday as 0, so I ended up with something like = Table.AddColumn(#"Added Custom2", "Is Weekend", each if Date.DayOfWeek([Date]) = 0 or Date.DayOfWeek([Date]) = 6 then "Weekend" else "Weekday")
Thanks really succinct and understandable, also you seem to be the only person (this refers to website rather than youtube tutorial) who has bothered to explain the #duration syntax, which is straight forward. ONCE YOU KNOW IT. So thanks again.
How do I make the start date to be the current month every time I refresh the data? I am working with an open order report and I am trying to pull only the 12 months ahead (opposite of a trailing 12 monthly report).
Create date table with parameter: = List.Dates(StrtDt, Duration.Days (EndDt - StrtDt) + 1, #duration(1,0,0,0)) StrtDt & EndDt have been imported from two tables in excel, dates can be changed in excel without opening the query. The dates are then drilled to form items, used in the above formula to create a datetable :)
Great video, I was looking for how to create a Month start and Month end fiscal column, which the Month ends at the last Saturday of each month and the Month starts at the following Sunday of each month. If possible please create another lesson.
I'm a complete newb to all things Power BI. In my attempt to follow the steps in this tutorial, I am not getting past Column1 generating from the New Query at 1:28. I appreciate any help (or if someone can tell me if I need to do something further with my StartDate and EndDate querries beyond creating them with the parameters of Type = Date, Suggested Values = Any value, Current Value = (the date I chose for start/end).
Yep! You can use Table.AddColumn(#"Add Day Offset", "Week Offset", each ( Number.From( Date.StartOfWeek( [Date], Day.Monday) )- Number.From( Date.StartOfWeek( Today, Day.Monday ) ) ) / 7, Int64.Type ) See the code examples at : gorilla.bi/power-query/date-table/#calendar-script
Hi I have created the startdate and enddate query using List.Min and List.Max and table name and column. When I created the calendar tables using these two, it's really long to get to the end. Samething each time I'm creating, the Year, Month, etc columns. Is this normal?
Thanks! Do you know how to create a calendar format 📅 with data from a table. I have data of events with different features and want to view them in a calendar, and be able to view the different features, e.g. event type, event lead etx
= List.Dates(StrtDt, Duration.Days (EndDt - StrtDt) + 1, #duration(1,0,0,0)) StrtDt & EndDt have been imported from two tables in excel. The dates are then drilled to form items, used in the above formula to create a datetable :)
Just start somewhere and add them to your repertoire. Slowly but surely you’ll remember. And you can google what you forget. No need to remember everything :)
Hello 👋 maybe you could help me about other topic but related to date as well. Problem : For my End date, I want to get the end of the month (date format) of my previous month as of today end of month local now values. Would this be possible? Example; Today EOM: Feb 28, 2022 Goal Prev EOM: Jan 31, 2022 Hope you can help me resolve this one. Many thanks 😊
Hi Arya, One way is to : 1. Use Date.StartOfMonth on your date 2. Then Date.AddDays with-1 as parameter to retrieve the previous day Hope that helps! 🔥
@@BIGorilla Hi, thank you for your response, much appreciated :) So basically I tried it on my end and it seems that there something wrong , could further help on this. Below is my current synxtax = Date.StartOfMonth(Date.AddDays(DateTime.Date(DateTime.LocalNow()),-1)) Output is this 01/02/2022
Oh.... you hid the setting the (StartDate, EndDate) in the code further down! Referencing a column name in a table results in a list. To get the latest sales date we can therefore use the List.Max function: = List.Max( Sales[Date] ) With this parameter in place, you can now reference the MaxSalesDate instead of the EndDate Parameter.
Your voice is so calm and smooth. Way better than some accents. I enjoy listening, Thank you
Fantastic lesson -much simpler and effective than many other lesson I wasted my time watching. Thank you for posting this!
Glad to hear that!
Amazing lesson. Also really really helpful that you wrote the code for relative week in power query. You are a Jedi Master
Great video! It would help to explain how to set up the Date Parameters at the first of the video.
Yea i have this question as well
This is everything I needed! Thank you!
For your reference, my default Canadian setting showed day of week as Saturday as 6 and Sunday as 0, so I ended up with something like
= Table.AddColumn(#"Added Custom2", "Is Weekend", each if Date.DayOfWeek([Date]) = 0 or Date.DayOfWeek([Date]) = 6 then "Weekend" else "Weekday")
Omg! in your blog you have more information about the topic, and with lot of details. Thanks for sharing your knowledge in Power query / BI
Thanks really succinct and understandable, also you seem to be the only person (this refers to website rather than youtube tutorial) who has bothered to explain the #duration syntax, which is straight forward.
ONCE YOU KNOW IT. So thanks again.
How do I make the start date to be the current month every time I refresh the data? I am working with an open order report and I am trying to pull only the 12 months ahead (opposite of a trailing 12 monthly report).
Great tutorial, Rick! Very informative, and useful. Thanks!
Create date table with parameter:
= List.Dates(StrtDt, Duration.Days (EndDt - StrtDt) + 1, #duration(1,0,0,0))
StrtDt & EndDt have been imported from two tables in excel, dates can be changed in excel without opening the query.
The dates are then drilled to form items, used in the above formula to create a datetable :)
Great video and explanation. Thank you.
Thank you! This is very useful 😃.
Great video, I was looking for how to create a Month start and Month end fiscal column, which the Month ends at the last Saturday of each month and the Month starts at the following Sunday of each month. If possible please create another lesson.
Excellent! Thank you!
🙏🙏
Thank you for this
Do you need to reference start and end dates parameters from calendar table?
Or isn it picked up automatically?
could you please share how to calculate ISO week number
What if my columns are datetime? I also need to work with time, beside date.
Thanks! follower subscriber
I'm a complete newb to all things Power BI. In my attempt to follow the steps in this tutorial, I am not getting past Column1 generating from the New Query at 1:28. I appreciate any help (or if someone can tell me if I need to do something further with my StartDate and EndDate querries beyond creating them with the parameters of Type = Date, Suggested Values = Any value, Current Value = (the date I chose for start/end).
Hey Sarah. Please have a quick look at my blog, where you will find exactly how to create the parameters: gorilla.bi/power-query/date-table/
Enjoy’
Is there and easy way to create a relative week index or offsets to this calendar?
Yep! You can use
Table.AddColumn(#"Add Day Offset", "Week Offset", each ( Number.From( Date.StartOfWeek( [Date], Day.Monday) )- Number.From( Date.StartOfWeek( Today, Day.Monday ) ) ) / 7, Int64.Type )
See the code examples at : gorilla.bi/power-query/date-table/#calendar-script
Hi I have created the startdate and enddate query using List.Min and List.Max and table name and column. When I created the calendar tables using these two, it's really long to get to the end. Samething each time I'm creating, the Year, Month, etc columns. Is this normal?
What about if my week stars on Sunday instead of Monday?, like from sunday to saturday.
Thanks. How do create the StartDate and EndDate parameters?
Hey Ian,
You can do that at the 'Manage Parameters' button in the ribbon.
Cheers,
Rick
Great video!
Is there a easy way to add columns to show if a day is in the last month, or in the last 6 months or in the last year?
Thanks! Do you know how to create a calendar format 📅 with data from a table. I have data of events with different features and want to view them in a calendar, and be able to view the different features, e.g. event type, event lead etx
Thank you, sir.
Thank you!
Instead of using parameters, how can I import the Start Date and End Date from Excel (user inputs) and use those dates? Thanks.
= List.Dates(StrtDt, Duration.Days (EndDt - StrtDt) + 1, #duration(1,0,0,0))
StrtDt & EndDt have been imported from two tables in excel.
The dates are then drilled to form items, used in the above formula to create a datetable :)
How do I create a function to count business hours?
Nice, now can you show us what we can do with this calender query?
How to know which function to use and how to remember all these functions please?
Just start somewhere and add them to your repertoire. Slowly but surely you’ll remember. And you can google what you forget. No need to remember everything :)
@@BIGorilla Thank you Sir
Hello 👋 maybe you could help me about other topic but related to date as well.
Problem : For my End date, I want to get the end of the month (date format) of my previous month as of today end of month local now values. Would this be possible?
Example;
Today EOM: Feb 28, 2022
Goal
Prev EOM: Jan 31, 2022
Hope you can help me resolve this one. Many thanks 😊
Hi Arya,
One way is to :
1. Use Date.StartOfMonth on your date
2. Then Date.AddDays with-1 as parameter to retrieve the previous day
Hope that helps! 🔥
@@BIGorilla Hi, thank you for your response, much appreciated :)
So basically I tried it on my end and it seems that there something wrong , could further help on this. Below is my current synxtax
= Date.StartOfMonth(Date.AddDays(DateTime.Date(DateTime.LocalNow()),-1))
Output is this
01/02/2022
@@aryastark5886 date.adddays should come first in the formula (so applied last). And before the date.startofmonth.
👌
@@BIGorilla Omg it works !! 🔥🔥 Thanks so much for your help! 😊
Hi Rick, i want Monday to be 1 and Sunday to be 7, instead of being 0 to 6. How?
You take regular formula and add + 1 to it. There's no other way unfortunately...
how to create quarter
💯👍
Oh.... you hid the setting the (StartDate, EndDate) in the code further down!
Referencing a column name in a table results in a list. To get the latest sales date we can therefore use the List.Max function:
= List.Max( Sales[Date] )
With this parameter in place, you can now reference the MaxSalesDate instead of the EndDate Parameter.
Pl creat calander table
👍👍🌹🌹
Fiscal week number
I mean like Q1