DATE TABLE for Power BI using Power Query

แชร์
ฝัง
  • เผยแพร่เมื่อ 30 มิ.ย. 2024
  • Learn how to create a date table in Power BI using Power Query. If you just want a quick solution you can copy over the code from the download link below.
    Download file here datatraining.io/powerbi-how-to
    --------------------------------
    📊 TRAININGS 📊
    ---------------------------------
    Power BI Design 4 Weeks Transformation Program my.datatraining.io/pages/powe...
    Power BI Essentials datatraining.io/powerbilearni...
    Business User Training datatraining.io/powerbi-busin...
    For Custom Trainings and Consulting email directly support@datatraining.io
    ---------------------------------
    ⏱️ TIMESTAMPS ⏱️
    ---------------------------------
    0:00 Intro
    0:41 Why do you need a custom date table
    3:21 Creating a date table using power query
    16:21 Connecting a date table and creating a date hierarchy
    20:37 Reusing a date table template
    21:14 End
    ---------------------------------
    😍 JOIN 😍
    ----------------------------------
    Join bit.ly/4b453bi
    Subscribe bit.ly/31MnQGO​
    Insta / howtopowerbi
    LinkedIn / basdohmen
    TikTok / how.to.power.bi
    X / howtopowerbi
    fb / howtopowerbi
    Threads www.threads.net/@howtopowerbi
    Newsletter datatraining.io/newsletter
    ---------------------------------
    👇 CHECK THIS OUT! 👇
    ---------------------------------
    💻 My gear amzn.to/47F21Yc
    📚 Power BI books MUST READ! amzn.to/3tUfFcj
    💡 General books I recommend amzn.to/48YNo33
    🎶 Music for my videos www.epidemicsound.com/referra...
    🚀 For growing on TH-cam: www.tubebuddy.com/bas
    🏄 Stuff I use daily amzn.to/3HqfMQ2
    * Above are affiliate links, which means at no additional cost to you, if you make a purchase using these links we will receive a small commission. It supports us and helps us to continue making more How to Power BI videos!
    Thanks for being a part of this channel and all your support! 💪 🙏
    #HowToPowerBI​ #PowerBI​ #DataTraining​
    #powerbidesktop​ #powerbitraining​ #powerbideveloper​ #DAX
  • วิทยาศาสตร์และเทคโนโลยี

ความคิดเห็น • 160

  • @trietn2921
    @trietn2921 ปีที่แล้ว +3

    This is the best explanation I've come across on how to do this. I loved how you showed the actual steps so that I could follow along and explained what each step was doing. I'm not a coder, but I could follow this. I like how you went back at the end and showed how it appeared in the data field.

  • @graemegourlay2850
    @graemegourlay2850 2 ปีที่แล้ว +18

    Brilliant. Exactly what I needed to do. Optimal pace on your tutorials; you get straight to the things that matter and crack through them in a methodical and logical way. Excellent stuff.

    • @HowtoPowerBI
      @HowtoPowerBI  2 ปีที่แล้ว +1

      Thank you for the kind words Graeme! 😀

  • @simon5296
    @simon5296 11 หลายเดือนก่อน +18

    Thanks for the video. One small suggestion is that, it would be much easier to follow if you could make your mouse cursor more visible. I had a hard time to see where did you click sometimes.

  • @alainluong3305
    @alainluong3305 ปีที่แล้ว

    I've just discovered this Channel, i realize I have at least 2 years of knowledge to catch up. And with your vids which are wisely structured and dense I'll catch up fast. Big thanks !

  • @kookooiam6034
    @kookooiam6034 ปีที่แล้ว

    Thank you so much for taking the time to walk through this date creation. I have seem many different videos and yours is the best, easiest to understand by far and you walk through every possible issue which I just love and very much appreciate!!!

  • @es8560
    @es8560 2 ปีที่แล้ว +3

    It took me 180 mins to digest this awesome 21 mins video yesterday. I forgot to say "thank you" and LIKE. So I'm coming back to revisit the topic and do I was supposed to do yesterday.😂 Excellent approach to explain the topic in a digestible way!

    • @HowtoPowerBI
      @HowtoPowerBI  2 ปีที่แล้ว

      :):) thank you so much for taking the time!

  • @codelemon4327
    @codelemon4327 2 ปีที่แล้ว +2

    Why am I just seeing this?! This is amazing, I really appreciate it.

  • @joaomartins2431
    @joaomartins2431 9 หลายเดือนก่อน

    This is the best explanation I've come across on how to do this.

  • @elishmuel1976
    @elishmuel1976 9 หลายเดือนก่อน

    Awesome! Here I thought I had to create a new Date Table for every range I wanted to look at! Thanks for helping me create my first Date Table!

  • @fdu6
    @fdu6 8 หลายเดือนก่อน

    I know I'm late, but you saved my butt. You are the best

  • @mamunkhandakar944
    @mamunkhandakar944 ปีที่แล้ว

    Thank you! It was great, and easy to follow tutorial.

  • @sonalichoudhury_jobseeker
    @sonalichoudhury_jobseeker ปีที่แล้ว +2

    Why this video got only 1k likes... it should be 1million likes.. u deserves it... 😃😃.. nice explanation.. "New subscriber added!!"

  • @CaribouDataScience
    @CaribouDataScience 11 หลายเดือนก่อน

    Thanks, the PQ method was what I was looking for

  • @constantinoooo
    @constantinoooo ปีที่แล้ว

    Man, you are so SMART and HANDSOME! 👏🏼I think I fell in love here. Thanks for this video! It helped me a lot!

  • @georgecook5120
    @georgecook5120 ปีที่แล้ว

    Thanks for this video. It was very helpful!

  • @samiraslan9735
    @samiraslan9735 3 ปีที่แล้ว +2

    Man I love your videos, keep the hard work up.

    • @HowtoPowerBI
      @HowtoPowerBI  3 ปีที่แล้ว

      I appreciate that! thx Sami! 😃 will do

  • @WooS1986
    @WooS1986 ปีที่แล้ว

    Your videos are awesome. You are very clear and your voice remember me to jhon (the father in this is us). Its very relaxing and nostalgic i dont know

  • @rokiczaaa
    @rokiczaaa 9 หลายเดือนก่อน

    I needed this film so much. Thanks :D

  • @cym0n
    @cym0n ปีที่แล้ว

    Thank you! Very helpful!

  • @kongklas
    @kongklas ปีที่แล้ว

    Good afternoon, Bas (and thank you for sharing you knowledge on the web). Your week_id-column really BLEW MY MIND and open SO many new doors for me regarding things that is possible in BI. I can't understand why no one else (as far as I have found) has done this before! Since this video was uploaded, do you have any new tips/tricks regarding creating this (holy) date table? :) Best regards, Norway!

  • @ryanbartlett672
    @ryanbartlett672 ปีที่แล้ว

    Very informative. Thanks!

  • @sammoss147
    @sammoss147 4 หลายเดือนก่อน

    This has been so helpful thank you! Would you be able to do a time table tutorial as well?! I'm working with call center data which is proving to be challengng!!

  • @CJ-jc8tn
    @CJ-jc8tn 3 ปีที่แล้ว +3

    Bas - Thank you for this lesson. The one question I have with creating a date table is whether to use Power Query or DAX. I've seen videos on both approaches and I've never really understood the pros and cons of both approaches. If you have any suggestions or tips on this topic, I'd appreciate your insight. Thanks.

    • @HowtoPowerBI
      @HowtoPowerBI  3 ปีที่แล้ว +5

      Ey CJ, it is more a matter of preference for the datetable. I usually create and modify the tables in PowerQuery when there is no good reason to do it with DAX.

  • @franziskaklingberg6857
    @franziskaklingberg6857 3 ปีที่แล้ว +1

    This was very helpful!
    Could you do one on adding a time table and then add this to the hierarchy?

    • @HowtoPowerBI
      @HowtoPowerBI  3 ปีที่แล้ว +1

      thx for the idea Franziska, will put it on my list! 😀

  • @krzysztofjasion8549
    @krzysztofjasion8549 ปีที่แล้ว

    Great lesson. Tank you.

  • @metecakmak376
    @metecakmak376 2 ปีที่แล้ว

    Great Content man thank you!!! I have a question for you, I have a table that contains Year, month, and day columns separately they are not in date form by the way. How can I convert them into a single date column?

  • @humayunshahzad9181
    @humayunshahzad9181 ปีที่แล้ว

    Helps a lot. Thanks Dear

  • @FernandoRamirez-ce2ou
    @FernandoRamirez-ce2ou 2 ปีที่แล้ว

    Awesome, that's exactly was what I was struggling with thx

    • @HowtoPowerBI
      @HowtoPowerBI  2 ปีที่แล้ว

      Glad to hear it helped! 😀 thx for watching Fernando

  • @AndreasNorway
    @AndreasNorway 8 หลายเดือนก่อน

    This was really helpful. Thank you!

    • @HowtoPowerBI
      @HowtoPowerBI  8 หลายเดือนก่อน +1

      Glad to hear you think so! Thank you for your support:)

  • @svetlucemo4448
    @svetlucemo4448 2 หลายเดือนก่อน

    Thanks for the video, it is really useful, but not beginner friendly. There might be some crucial steps missing.

    • @HowtoPowerBI
      @HowtoPowerBI  2 หลายเดือนก่อน

      Which steps are missing?

  • @julioms23
    @julioms23 3 ปีที่แล้ว +3

    Good lesson!!

  • @user-nd4cq1op8u
    @user-nd4cq1op8u ปีที่แล้ว

    Hello. Thank you for the video. I have a question to ask you. What part should I adjust to make a date table that has a week interval from the start date to the present date?

  • @user-jk7zt2wt5j
    @user-jk7zt2wt5j ปีที่แล้ว +1

    Many Thanks!
    this is a beneficial video !!

  • @adiletyessaliyev4922
    @adiletyessaliyev4922 ปีที่แล้ว +4

    Hi Bas! As usual very informative and helpful. But I've gotta question - is it possible to get the max date from fact table in report instead of addin end date manually?

    • @JoepR-rs4br
      @JoepR-rs4br 9 หลายเดือนก่อน +1

      Date.From(List.Max(facttable[columnname]))

  • @adrotj
    @adrotj 2 ปีที่แล้ว

    Very helpful Bas!

  • @manishtekale5449
    @manishtekale5449 ปีที่แล้ว +2

    Can you please make a video on how to slice between calender year and financial year

  • @brucemcminn5169
    @brucemcminn5169 ปีที่แล้ว +1

    Another great video!! I have one question. At 16:45 all of the added columns appear to be the Date data type. But at other places (like 11:21) they are still text or whole number. Howe are you managing this conversion? If I simply change the data type, PBI converts the number to a date based on that value (i.e. 2022 becomes Friday, July 14, 1905). Thank you!

  • @vimukthiamarasena6264
    @vimukthiamarasena6264 2 ปีที่แล้ว

    Great video.. is there any special advantage of using Power Query Date table over DAX date table?

  • @rogeriofernandes8867
    @rogeriofernandes8867 3 ปีที่แล้ว

    Hi Bas, I like all you videos, we just start using Power BI in my organization and they've been very useful. And this one just land on the right time, I just start a new PBI project two days ago and I'll need a personalized calendar (weeks, short names, etc). Thanks once more. Is it possible to have the final code?

    • @HowtoPowerBI
      @HowtoPowerBI  3 ปีที่แล้ว +2

      thanks Rogério! awesome, I am happy the videos helped! I included the code in the download link (see description section).

  • @Bangles_and_Beads
    @Bangles_and_Beads 11 หลายเดือนก่อน

    I spent at least one hour to view and practice from what I learnt from this video, at beginning I copied the codes from the end of session into advanced PQ editor, it didn't work, so I do it again from beginning, also learnt the trick to bulk update un-summaries columns 😇

  • @shivaninaidoo683
    @shivaninaidoo683 ปีที่แล้ว +1

    Hi Bas, awesome video can my Quarters be different. For example, if my Start Date is in July-Sep for Quarter 1, Oct- Dec= Quarter 2 ?

  • @fr0rg
    @fr0rg ปีที่แล้ว +1

    Would help to see other areas of the screen. Lost when you started creating the list of dates after commenting the variables

  • @gambu4810
    @gambu4810 2 ปีที่แล้ว +1

    You are simply brrrrrrriliant. You always knock my socks off. Can l give you my daughter?

  • @mahendrasingh7284
    @mahendrasingh7284 4 หลายเดือนก่อน

    Thanks for the amazing knowledge.
    Is there any change we can set start date dynamically

  • @GiovannaSabanalarga
    @GiovannaSabanalarga ปีที่แล้ว +1

    Hello! Thank you for the video! In your relationships will this only work for order date? If so, how do you make the connection work for your other dates like Due Date or Ship Date?

    • @svendb7
      @svendb7 ปีที่แล้ว

      Yeah, like do you just create multiple relationships and all works out fine and dandy?

  • @r0b1
    @r0b1 3 ปีที่แล้ว +2

    Hi Bas! Great video again. If i have 1million row, in minutes resolution, and want to slice, filtering, hours, days etc, what is the best solution? Create related one date and one time table, or just datetime table?

    • @HowtoPowerBI
      @HowtoPowerBI  3 ปีที่แล้ว

      the more unique values, the worse the data will be compressed. Therefore it is better to split it and make sure to truncate it to minutes (if you dont need seconds or hours if you dont need more granularity).

  • @diederikvankolfschoten47
    @diederikvankolfschoten47 3 ปีที่แล้ว +3

    Hi Bas, great series. I have one question regarding the weeknumber: this seems to be the US weeknumber. Is there an equivalent like the Excel ISOWEEKNUMBER for European weeks. Week no 1 hardly ever starts on jan 1st..... in Europe that is....

    • @HowtoPowerBI
      @HowtoPowerBI  3 ปีที่แล้ว +3

      Hi Diederik, there is no native function in Power Query that calculated the ISOWEEKNUMBER. However, you could write ( or google ;) ) a custom function . Here is an example I found that could work excel-inside.pro/blog/2018/03/06/iso-week-in-power-query-m-language-and-power-bi/

  • @erik8362
    @erik8362 9 หลายเดือนก่อน

    Is there any benefit in doing this wit PQ in stead of Dax? Usually I would set the start and end date based on the dataset max and min dates to get the correct date range.

  • @mihaiguramulta2667
    @mihaiguramulta2667 3 หลายเดือนก่อน

    Great Video!!! As always, your videos are full of excellent info!!!! But, how can I create a collumn that calculates the week number, knowing that the week starts always on saturday?? is it possible??? Thank you so much!!!

  • @sidneylc28
    @sidneylc28 2 ปีที่แล้ว +1

    Thanks for the tip, the reusable part is what adds value the most. I have followed the steps but the date column gets the icon of unique value, not as a date. does it affect the model? I couldn't add hierarchy with the drag option, only through the model view with right click add to hierarchy. Aparte from that, everything workas as expected.

  • @flipped9654
    @flipped9654 ปีที่แล้ว

    Nice Vid! How do you add Fiscal Year/Quarter as new column?

  • @yanelena8603
    @yanelena8603 2 ปีที่แล้ว

    I am a beginner and Bas's videos really help me a lot. I cannot find how to make multiple selections of the fields and cannot find Filed Properties. Could anyone, please, help me with that?

  • @es8560
    @es8560 2 ปีที่แล้ว

    Bas - Thank you for this video. I'm wondering if you can do a video on how to add fiscal year, fiscal QT and fiscal month. Say company with fiscal year ended June instead of Dec. It's a wish list.😃

    • @HowtoPowerBI
      @HowtoPowerBI  2 ปีที่แล้ว +1

      you can add further custom columns to the date table -> eg
      if [Month]>=FYstart then [Month]-(FYstart-1) else [Month]+(12-FYstart+1)

  • @malualves6955
    @malualves6955 2 ปีที่แล้ว

    Perfect!

  • @joserafaelfarfanfernandez899
    @joserafaelfarfanfernandez899 ปีที่แล้ว

    Great ! Thanks

    • @HowtoPowerBI
      @HowtoPowerBI  ปีที่แล้ว +1

      You are welcome thx for watching

  • @daviddecastro9653
    @daviddecastro9653 ปีที่แล้ว

    Solid tutorial Bas - awesome

  • @patrickdougherty2869
    @patrickdougherty2869 8 หลายเดือนก่อน +3

    FEEDBACK: For beginners your cropped screen view makes it hard to see HOW you got to where you are. Example after setting up the Start and End Date you start to write the function, but I cannot get the Start/End date values out of the fx editor

    • @HowtoPowerBI
      @HowtoPowerBI  8 หลายเดือนก่อน

      thanks for the feedback

    • @michaelg9359
      @michaelg9359 6 หลายเดือนก่อน

      Yes, how do you do this? I can't set up the end date. Thanks-

  • @Ronnet
    @Ronnet ปีที่แล้ว

    Am I correct to assume the automated date tables also don't allow you to use Dax code that let you compare month to previous month? Only after applying a custom table did such calculations work. But I'm not 100% on whether this is due to the automated date tables or something else.

  • @deshmukram623
    @deshmukram623 2 ปีที่แล้ว

    grreat videeo.. Bro, when i created a relationship with the date with my order_date in sales table , unfortunately it was not showing any hierarchy for the order_date column in my sales table

  • @drapidel
    @drapidel 9 หลายเดือนก่อน

    Bas, Thanks. I'm discovering your video, its brilliant. I nevertheless have a problem: I cannot sort the column Month by the column MonthNo. (it works for quarter). PBI says that there can't be more than one value in MonthNo for the same value in Month. Many Thanks

    • @HowtoPowerBI
      @HowtoPowerBI  9 หลายเดือนก่อน

      well,.. :p the error message gives it away .. makes sure that for each monthname there is only 1 corresponding monthnr .. also make sure there are no blanks

  • @claudinebeunens8721
    @claudinebeunens8721 2 ปีที่แล้ว

    Great video, thanks! You had 3 date fields in your dataset, same as in my dataset. I can link 1 to the Dim_Date but what if we want to have the same for the other date columns as well?

    • @HowtoPowerBI
      @HowtoPowerBI  2 ปีที่แล้ว +2

      Thank you Claudine! 😊You can make relations on all 3 (2 will be inactive, 1 active). In you measure you can then use the inactive relation ships as follows:
      = CALCULATE(SUM(fctSales[SalesAmount]), USERELATIONSHIP(fctSales[OrderDate], dimDate[Date]))

    • @claudinebeunens8721
      @claudinebeunens8721 2 ปีที่แล้ว +1

      @@HowtoPowerBI Great, this will overrule the primary connection! Thank you for providing this insight!

    • @user-mh8ts8op8t
      @user-mh8ts8op8t 2 หลายเดือนก่อน

      @@HowtoPowerBI Please could you do a video on this?

  • @thierrythomas6640
    @thierrythomas6640 2 ปีที่แล้ว

    Hi Bas,
    Very nice vidéo !
    How can I add a column where the dates like 12/25 or 12/31 or specific date that I choose appear as 1 and other date as 0 ?
    With this column, I could retrieve non working days from calculations.

    • @juanm555
      @juanm555 10 หลายเดือนก่อน

      for working days count, I think there is a Net working days function now, for adding those kinds of holidays, I think you would have to import the days and maybe merge them into the calendar table to create a column that has that data

  • @vkncf7026
    @vkncf7026 2 ปีที่แล้ว +1

    This is really good information. Is it possible to highlight federal holidays on a line chart with a weekly sales and date table?

    • @HowtoPowerBI
      @HowtoPowerBI  2 ปีที่แล้ว

      thank you!! 😀 how would you want to show it, as a mark on the line?

    • @vkncf7026
      @vkncf7026 2 ปีที่แล้ว

      @@HowtoPowerBIThanks for the reply. As a Mark for sure....such that and a text such that over the line you can see for example we had Thanksgiving hence the reason sales were up or down. Only want to use it as a reference

  • @VenemAcademy
    @VenemAcademy ปีที่แล้ว +1

    Hi Bas, this is extremely helpful. Thnx! But ... (there is always a But 😉) ... the schoolorganisation where I work (vmbo/mbo in the Netherlands) we're so occupied teaching that it feels like the ink on one Q-report is still wet or the next Q-report has to be written. So we decided to split the year in thirds (tertial reporting). It gives us time to breath (and teach). How can I add such a tertial number column to my date table? Is there a mod function in Power Query or DAX e.g. (DayNr mod(122))+1?

    • @VenemAcademy
      @VenemAcademy ปีที่แล้ว

      ... nailed it Bas (thnx, inspired by Your other videos) ... used Number.IntegerDivide 🙂 based on the daynumber of the year. Made it even more dynamic, in case my boss changed his mind about number of reports per year 🙈

  • @TeejTubeU
    @TeejTubeU ปีที่แล้ว

    Why, after clarifying the sort order for the individual columns, like you see at 20:29, does it order the Months alphabetically and not in the order that they occur when covering multiple years?

  • @scramiro1
    @scramiro1 3 ปีที่แล้ว

    Could you do a best practice video for creating data tables? For instance, I used DAX, but I think the italians recomend to use string instead of integers? Also, for instance, in filter cards, I don't know how to order the years o select automatically always the last year. Thanks!

    • @HowtoPowerBI
      @HowtoPowerBI  3 ปีที่แล้ว +1

      Hi Sergio, thanks for the ideas! I will probably do a video soon on creating a date table with dax as well and touch upon the topics you mentioned.

    • @scramiro1
      @scramiro1 3 ปีที่แล้ว +1

      @@HowtoPowerBI thanks. I've been told that content on filter cards can't be sorted. That's why mainly people prefer to create their own filter pane. You did a video on filter pane, but what are the benefits Vs filter pane? Why should I create a custom filter pane? More ideas 😉

  • @sr5726
    @sr5726 ปีที่แล้ว

    for inserting new step do you use a keyboard shortcut ?

  • @denisegarcia8464
    @denisegarcia8464 2 ปีที่แล้ว +1

    Can my Start Date and End Date be different. For example if my Start Date is in June and End Date May will this still work?

  • @Reventrant
    @Reventrant 3 ปีที่แล้ว +1

    Is this the method you recommend when there is no end date? I analyze production data and there is no end date in sight. We've been in operation for 20+ years already. I usually use CalendarAuto but do you think there is a better way?

    • @HowtoPowerBI
      @HowtoPowerBI  3 ปีที่แล้ว +1

      Hi Todd, you can make the end date dynamic for example by using List.Max([Date Column]) . I generally transform and create my tables in power query when possible. However, that is more of a personal preference. I don't think there is a performance difference between the two methods.

    • @Reventrant
      @Reventrant 3 ปีที่แล้ว

      @@HowtoPowerBI - Awesome, thanks for the List.Max([Date Column]) tip.

  • @ObinnaChigboguNwokolo
    @ObinnaChigboguNwokolo 5 วันที่ผ่านมา

    Lovely Video. Followed all through. Is this method still okay to apply today or is there a better way boss?

    • @HowtoPowerBI
      @HowtoPowerBI  5 วันที่ผ่านมา +1

      this method still is fine

  • @AhmedElShafeey
    @AhmedElShafeey ปีที่แล้ว

    I couldn't use analytics trend or forecast in my visualization 'ex. line chart" when I use the calendar table date column that related to data table "ex. sales"

  • @p3trolhead
    @p3trolhead 3 หลายเดือนก่อน

    thank you

    • @HowtoPowerBI
      @HowtoPowerBI  3 หลายเดือนก่อน

      You're welcome

  • @D4rkm00r
    @D4rkm00r 2 ปีที่แล้ว

    This is so whizzy! How did it take me a year to find? Thanks !

  • @saurya1979
    @saurya1979 4 หลายเดือนก่อน

    I lost the slicer style "between" when I use the hierarchy using this approach. It now just gives me Vertical or DropDown options for slicer style. Is there a way to make a slicer style "Between" using this table?

  • @andreasschmidt7665
    @andreasschmidt7665 3 ปีที่แล้ว

    Dear Bas, again a very helpful video. I've been looking for a long time for a solution to have a field in the calandar table descending sort year - month number. The reason is a descending sort in a matrix. Have you got any idea how to bulld such a field ?

    • @HowtoPowerBI
      @HowtoPowerBI  3 ปีที่แล้ว

      Hi Andreas, thank you! you could build a separate year desc and year desc sort column. Then do that as well for the month. I added an example in the download (see description).

    • @andreasschmidt2766
      @andreasschmidt2766 3 ปีที่แล้ว

      @@HowtoPowerBI Hi Bas, thanks a lot for immediate reply. I show in my matrix only one field Year/month (Text.End(Text.From([Year]),2) & "-" & [Month]) and I have build the appropriate sort field with ([MonthNum] + [Year]*100). However, I could not find to get the Year/Month field converted in descending order.

    • @HowtoPowerBI
      @HowtoPowerBI  3 ปีที่แล้ว

      @@andreasschmidt2766 I have updated the example in the download file (datetable_sort_desc.pbix) . The main idea is that you have a YearMonth Desc sort order (multiply by one) (make sure it has data type whole number). Let me know if it is clear from the updated example.

    • @andreasschmidt7665
      @andreasschmidt7665 3 ปีที่แล้ว

      @@HowtoPowerBI Hi Bas, you are very smart, the key is the YearMonth Sort field. Yes it is very clear now. Thanks a lot again.

    • @amuthavanoptisol9997
      @amuthavanoptisol9997 2 ปีที่แล้ว

      @@HowtoPowerBI you are genius ! I'm breaking my head for this. Thankyou.

  • @GianAgassi
    @GianAgassi 2 ปีที่แล้ว +1

    If I want to have a custom date table for every date field (order date, due date, ship date in your example) , do I need to create 3 custom date tables? Coming from QlikSense, where the "autocalendar" script works like a charm and it is customizable, this seems inefficient

    • @HowtoPowerBI
      @HowtoPowerBI  2 ปีที่แล้ว +2

      I would create 1 - and then create a relation on all 3 date fields (no 2 and 3 will b inactive). You can then write a measure that uses the relation you like. For example:
      CALCULATE (
      SUM ( fctSales[Sales] ),
      USERELATIONSHIP (
      fctSales[Due Date],
      'dimDate'[Date]
      )
      )

    • @GianAgassi
      @GianAgassi 2 ปีที่แล้ว

      @@HowtoPowerBI thank you - in my difficult quest from Qlik to PowerBI, I am drilling down into the data modelling differences and I am very much scared by the loss of the associative modelling :( The fact that custom measures are the best way to cross-filter slicers, given the lack of many to many relationships... probably the result can be the same in 99% of the cases, but building the data model in Power BI seems to require much more knowledge and attention and a lot of workarounds to make things work as one would expect, am I wrong?

  • @alphamaniac9411
    @alphamaniac9411 2 ปีที่แล้ว

    Great! How do you change the Quarter to a Fiscal quarter which starts July 1st?

    • @HowtoPowerBI
      @HowtoPowerBI  2 ปีที่แล้ว +1

      THX! 😀
      First add fiscal month:
      if [Month]>=StartOfFiscalYear then [Month]-(StartOfFiscalYear-1) else [Month]+(12-StartOfFiscalYear+1)
      then fiscal quarter:
      divide by 3 and round up

  • @petesansom5737
    @petesansom5737 2 ปีที่แล้ว

    What would be the benefit of using this technique over DAX? Normally I just create a new table and drop some DAX like Calendar = ADDCOLUMNS((CALENDAR("01-01-2022", "31-12-2022")), "Year", FORMAT([Date], "YYYY"), "Week", WEEKNUM([Date], 1) ...... etc

  • @kasperkaspersen361
    @kasperkaspersen361 2 ปีที่แล้ว

    If I want to list all dates between a larger interval etc. 50 years how would i solve the 365 or 366 days issue? Will +1 in the Duration.Days formula fix this for all years?

  • @kunalgupta5667
    @kunalgupta5667 ปีที่แล้ว

    Hi sir, learn a lot from your videos 😊

  • @matthewbelitz2950
    @matthewbelitz2950 10 วันที่ผ่านมา

    thanks for the video, been following along well but i'm getting an error at about 16.5 minutes in when it's time to name it...because it doesn't like in this step that i'm trying to subtract a date from a datetime value: = Duration.Days(Duration.From(EndDate-StartDate))+1. How do i get around this?

  • @shrikantthakur7127
    @shrikantthakur7127 2 ปีที่แล้ว +1

    Nice please Add Fiscal Year April 21 to Mar 22

  • @bradblankenship8157
    @bradblankenship8157 2 ปีที่แล้ว

    How do you add a column that concatenates Year and Quarter?

  • @recipequeen7850
    @recipequeen7850 ปีที่แล้ว

    Hello, so I'm having this problem (i'm new to power bi). I have let's say types of loan portfolios in one column and then i have dates horizontally and information is filled out under each date for all portfolios. and I'm having problem with this format, because power bi doesn't recognize dates that are horizontally spread, at least not automatically, and I can't seem to build a chart for this time series and these portfolios, when i restructure the data and put all the data vertically including dates then it recognizes the dates. If someone has a solution much appreciated :)

  • @rokiczaaa
    @rokiczaaa 9 หลายเดือนก่อน

    How to combine dataparts and texts? I'd like to create yyyymm column (and similar)

  • @UsaR93
    @UsaR93 ปีที่แล้ว

    Hi Bas,
    When I was looking for custom date table I found this code to create list of dates, the easiest:
    Source = {Number.From(#date(2019,1,1))..Number.From(#date(2020,12,31))},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}})
    What do you think about it?

  • @2011mircea
    @2011mircea 2 ปีที่แล้ว +1

    When I try to Sort Day by column DayNo, I get the error "We can't sort 'Day' column by 'DayNo'. There can't be more than one value in 'DayNo' for the same value in 'Day'. Please chose a different column for sorting or update the data in 'DayNo' ". Does anybody know why? For the other columns the change of default sorting is working

  • @joefromdc
    @joefromdc 2 ปีที่แล้ว +1

    is there a way to show the current and previous month?

    • @HowtoPowerBI
      @HowtoPowerBI  2 ปีที่แล้ว +1

      your first need to build the date table like in the video .. then you can do the time/period calculations using dax (see vid here th-cam.com/video/I8Y5fBGwqeQ/w-d-xo.html )

  • @irfanabbassi9202
    @irfanabbassi9202 2 ปีที่แล้ว

    What If my Fiscal Quarter (q1) start from O1-July?

  • @monicanakka3824
    @monicanakka3824 2 ปีที่แล้ว

    How to select all the fields?plz help

  • @SoOS186
    @SoOS186 ปีที่แล้ว

    The function works great.. but when i add it to my data i get errors which makes the query impossible to load. I replaced errors and nulls with zeros. Still same. Im not able to use the function. My dates are dates type and i have no negative results 😢. Help!

  • @shreyavats7008
    @shreyavats7008 ปีที่แล้ว

    From where and which data you used ?

  • @josevaldesv
    @josevaldesv ปีที่แล้ว

    I didn't fully understand the reasons for hiding some columns and for disabling the date learning options

  • @mystrocs
    @mystrocs ปีที่แล้ว +2

    How did you solve: Can't Sort "Day" by "DayNo." due to multiple values for each entry..?

  • @Soulenergy31
    @Soulenergy31 2 ปีที่แล้ว +1

    13:15 dding comments to power query editor

  • @svendb7
    @svendb7 ปีที่แล้ว

    Whoa, but what about the range of dates in my sql queries? My start dates aren’t aligned eeek!

  • @thebanker06
    @thebanker06 7 หลายเดือนก่อน

    I tried to mark as date table but field is grayed out ! Is there a problem

    • @HowtoPowerBI
      @HowtoPowerBI  7 หลายเดือนก่อน

      maybe already marked as date table?

  • @mystrocs
    @mystrocs ปีที่แล้ว

    And what if you have more than 1 date column in your report? this threw everything off..

  • @JM-cd6iz
    @JM-cd6iz 10 หลายเดือนก่อน +1

    you lost me at 18mins13sec but from some further googling I see you must have right clicked to create the hierarchy. I appreciate all the videos but us newbies sometimes need a little more info. I agree with another user - can you make your mouse cursor more visible. Thanks

    • @cm386464
      @cm386464 5 หลายเดือนก่อน

      You can only click and drag in model view, I think it was correct when he made the vid but has since updated. GLHF

  • @dabarapraise8287
    @dabarapraise8287 3 หลายเดือนก่อน

    pls could you show your full screen next time.