Working with Time Periods in Power BI 

I started Power BI 2 months ago and watched your videos like crazy. I got certified on Monday, a big thank you guys because I could answer ,any questions thanks to your videos

We do something very similar. We added a sort column to ensure that the order or the slicer made more chronological sense.

This video was right on time :) I had this problem earlier today and this explanation simplified it and finally has me getting the number I expected! Thank you

Question: excellent video. How could you include code to also pull same period last year?

Please do a video using a calendar that isn't Fiscal. My company uses a periodic calendar (13 periods in a year). Thanks for your hard work, your videos have helped me lots.

Omg I was just and I mean today just trying to do a calendar function. Your tutorial was perfect timing. Thank you so very much for this post and the links.

Hello Guy. I just wish to thank you for this video. This has been super super helpful. Had a challenge on a model I'm working on and I didn't know how to even begin formulating the question in a way that the next person would understand. I just kept researching, and I stumbled on this video, followed the link you shared from Phil, and my problem was solved like!!! Thanks again, man.

Thank you, this was really helpful for me, I needed to find next month, and now it was easy to add that in the code. I have subscribed to your instructions videos now :)

Yesterday I came up with almost the same solution! I've got some monthly data and I had to filter, after choosing one month, all the months from that one to January, regardless of the year (kind of a custom sameperiodlastyear). Bidirectional filtering works perfectly when you know what you're doing (even though is bad practice!). Nice video as always! 👍🏻

Brilliant idea. I used a similar approach to define aging buckets (for expiry dates, invoice aging ... etc.) And as you said, I like to do all of these data acrobatics on the source. Make modeling easier for me.

0:58 Before watch, I use to create some columns on my date table to make this more transparent to user, for instance last N months, so they can select the last 3 months, or the current one selecting 0.

How to implement 15,45,60 minute time interval and display data accordingly?

I have a similar, yet bit different approach I have extra columns in my date table (IS_yesterday, IS_thisweek, IS_this month, IS_thisYear, IS_LTM, IS_last48hours, IS_last2years ... THEN I have a new table with this values so i can put them into a slicer for all data - this code was wery easy to do, and easy to add new column in date table (either on DW, on PQ, or as dax)

Hi! Let me push the limits a bit more ;)....how would you recreate the same Time Periods as Phil but with a future date? Unfortunately, for me, I have sales data for the next 12 months. Thanks in advance!

Excelent video! I got crazy once some time ago trying to make something similar

Hi Patrick :- this is an osum technique. But I have an use case for periods such as a combination of date and time Ex:Last hour,last 6 hours, last 12 hours, yesterday, last week, last month,last year. Can anything be done for this combination

This doesn't help when connected to analysis services multidimensional cubes. Is there a workaround using named sets or something similar when using Analysis Services multidimensional cubes as a source? Also any news on when Power BI will allow the use of named sets?

Hey you guys are amazing. I love watching your videos, Thanks for the awesome stuff😊 . On your PowerBI top menu I see External Tool, what is that? I don't see it on mine, little curious to know lol. You guys rock 🏆

Hi Patrick Sir, I hope you and your loved once are safe, I want to thank you personally for helping me and each one to learn and explore their talent in power BI career. watching your videos on Power Bi I have explored myself in career, thank you from bottom of my heart. Sir...now from my management side there is a ask to show the Daily, weekly and monthly in a single slicer in dashboard which can dynamically visual on selection. for that ask I'm Struggling a lot, my request to you is help in that or make a video on that so we can achieve the ask, it will be great help for my career. Thanks in advance

Hi I am having trouble to calculate the average between the difference of 2 dates. Let’s say delivery start vs eta. I have used averagex and then datediff. However as most of my rows don’t have a delivery start, when the calculation is made the average takes into account blank or “0”’s. So when I want to visualize it I have blank or no information. Pretty much I want a measure that only takes in count the dates with delivery dates on and average it so I can then group it by department. Hope this is clear

HI Patrick, love your videos, they make life as a beginner much less complicated. However, in your video you say the code for last week is easy, as a beginner not so much. Please can you share the code for the last 7 days? Thanks

Hi Patrick, I'm very new to PBI and learning a lot of tricks from you videos for my PBI works. I use the technique yes / no for the filter "last month" as this is so quick solution. I try to create the yes/no for "next month" but could not figure out. What do I have to replace the formula to get next month ? ECD_Next Month = IF ( DATEDIFF( opportunities[Est. Close Date], TODAY(), MONTH ) = 1, "yes", "no" ) Thank you!!!!

Thank you for your great content. Our time periods are not calendar fiscal Months but our month ends the last saturday of each month. I found the formula but I am having difficulty implementing it. (EOM2 = VAR A2 = 'Table'[Date] RETURN IF(A2>DATE(2011,12,31),IF(A2>IF(WEEKDAY(EOMONTH(A2,0),1)=7,EOMONTH(A2,0),EOMONTH(A2,0)-WEEKDAY(EOMONTH(A2,0),1)),IF(WEEKDAY(EOMONTH(EOMONTH(A2,0),1),1)=7,EOMONTH(EOMONTH(A2,0),1),EOMONTH(EOMONTH(A2,0),1)-WEEKDAY(EOMONTH(EOMONTH(A2,0),1),1)),IF(WEEKDAY(EOMONTH(A2,0),1)=7,EOMONTH(A2,0),EOMONTH(A2,0)-WEEKDAY(EOMONTH(A2,0),1))),EOMONTH(A2,0))

Why we don't just use the date as filter with setting that to relative!?

Is there anything for future time periods?

How about filter data by last 2 weeks dynamically? Please explain!

I've generally noticed DAX is quite faster than Power Query, especially in column creation like operation, want to know your reason why as you said it should be done PQ?

How do I create duration in start date

Hi Patrick, this is very helpful! This solves half of my problem though. I have a requirement that will allow my users to select predefined date periods (current month, last x months, last year, etc) and with flexibility of selecting between dates. For example, if any of the date periods are not selected, it is defaulted to a custom date range. Is that even possible?

Great video, but I found a bug. Only on the 1st of each month does the table give an error because "Yesterday" doesn't work. On the 2nd of the month it works again. Can someone tell me how to fix this?

I have a scenario like when I click on monthyear column in main report the drillthrough report should show last 12 months data based on monthyear selected in main report

Hi could you plz explain how to put slicer like ladt n days date for ex last 7 days last 15 days slicer date and from that we can show visual data

hi patrick, how do i filter based on the following time period: yesterday morning at 07:00am til today 06:59am?

What if you had connected to the fact table instead of the calendar? We would get rid of the bidirectional relationship

In same scenario how can I add Last weekend and Current Week ? Required DAX Expression

Helloo.. your videos are very good ..I am new to Power BI.. and have explored and implemented many things.. I need help in "How to use measure value to create a new table using dax" .. for eg if measure value is "abc" then new table= abc, if measure value="abc_1" , new_table=abc_1 .using dax in power bi...( where abc and abc_1are tables in pbi ). Thanks a lot

I work with the duration of swimmers' tests, I need to make a ranking for the tests but the duration format has not allowed me to do this order.

Hi, just a side question. If I add the "Month" from a date hierarchy to a filter/visualization how I can change the format of it to show the month as a number and not the name of the month?

what is Axis Date for? i couldn't see the use of it

This is great, but when I use a calculated measure that contains SAMEPERIODLASTYEAR it doesn't produce a result. Is there any way around that?

Thanks for the video, it was helpful....am having an issue, i tried to adjust the Yesterday's DAX Code to show for 28days but it return a single date and when i try it on a slicer it returns just value for that particular day not in addition to values from within the time period selected. how do i go about fixing this?

Hi Patrick - as usual, this is awsome. I have question at 3:21. How come you don't see count of items in Filter Pane. I would like to hide count of slicer items in Filter Pane. Please help me.

Hi Patrick.. can you please help me with this one? I have data that is spread across countries. The fiscal quarter are different in few countries. Q1 for America is from Jan, while Ireland has Q1 reporting from Dec. How can I have one slicer that can identify the country and show the quarters correctly?

What if I want to compare the last year same payperiod? I have a date table with a calculation for the pay period number because our 2021 first pay period started in December 2020 then the 2020 first pay period started December 2019. Does it mean that I need to have 2 calendar tables for each year so I can pull the 2020 pay period and compare to 2021 pay period? Thanks.

Hi, first of all nice videos. Time periods seem to be a difficult thing. I have a problem managing time periods with a free selectable start Date and Time and also with an free selectable end Date and Time For example there is a list of 9 Million Entries with DateTime Column and i want to show only from 06.09.2021 15:00:00 to 07.09.2021 02:30:00 How can I do this? The build in date filter is not that good because time is only adjustable in the AM/PM format and the slider filter can only handle dates and not time...

Hi guc and thank you for this great video. Just one question for me, how can we hide or suppress from the X axe the closed day (ie for a ytd lean analysis) thx in advance!

how do i create these time periods from the selected date?

Hello, how can I calculate the average of the last 5 minutes if the date is in date and minute format? of course I also have a date table with unique values! THX guys, Barna

Hi Patrick nice stuff as usual :-) What about translations of “today”or “tomorrow” etc ? I have a tabular model in SSAS 2017 ok place If I add this period table I guess localization would be impossible Any idea ?

Hey Patrick - nice video! Thank you! Question... what’s my best strategy in terms of DAX / PBI for managing time intelligence for a small table based on an SP list which has multiple date values per record (eg start / stop dates for phases of a project)? Should I create a calendar table and have one active relationship and multiple inactive relationships between the calendar table and the date fields from the SP list? Or is there an easier / better strategy? Any/all insights greatly appreciated! Tnx!!

Hi Patrick, is it possible to give year end date dynamically in TotalYTD DAX , so can u pls help me with this if there is a way.

Interesting approach, just one question if I may, why do you use this instead of the standard time-intelligence functions?

Hey Patrick! When doing the 'last week' calculated column, I noticed that it starts the week on a sunday. Is there any way to let the week start on a monday instead? :)

A simple question. Any reason why Datediff between 10/9/1979 and 2/9/2027 gives 47 in excel and 48 in Power BI DAX. Is there some known BI issue or I'm doing it wrong.

I'm sure I'm missing something here... I'm working with a dataset where one of the fields is the 'End Date' of an item and the report I have is to allow people to work with things that are coming towards that date or are past that date. The concepts and ideas in this video seemed ideal for application... however, on my report I put a slicer for the date, and changed the filter display to 'Relative Date' and 'Dropdown'.. It presented me with a beautiful filter that allowed people to change the filter to anything from 'Last 1 day' (or yesterday) to 'Next 1 day (tomorrow).. and has Calendar years, Calendar Months, Calendar weeks as well as normal years, months and weeks.. plus I can change the value from 1 to whatever I like.. Whats more is that it displays the date range for the selection.. and no DAX is required..

I read the title and was so excited. But no! What about 3 hours ago? Or 20 minutes ago? Those are Time periods. The stuff you did here are Date periods. ;(

That's if. This is where I hang out now.

Error when creating the Time Period Table says: "An argument of function 'DATE' has the wrong data type or the result is too large or too small."

DON'T EVER USE THIS METHOD unless you want to calculate MOM / YOY values. This type of period filtering is not the same as filtering the date on the slicer with the RELATIVE DATE option.

This isn't simple. Might be worth you grading your videos for difficulty.

You should just go to the point as opposed to so many small talks