Power BI: Use DAX to get relative dates
Вставка
- Опубліковано 21 сер 2024
- Patrick looks at how you can get relative dates, in Power BI Desktop, without using the relative date slicer or relative date filters. You just need to sprinkle a little DAX into Power BI Desktop to get this going!
********
LET'S CONNECT!
********
-- / guyinacube
-- / awsaxton
-- / patrickdba
-- / guyinacube
-- / guyinacube
-- guyinacube.com
**Gear**
Check out my Tools page - guyinacube.com...
#PowerBI #RelativeDates #GuyInACube
![Handling Multiple Dates in Power BI With DAX [2022 Update]](http://i.ytimg.com/vi/eiDWMqgKlZI/mqdefault.jpg)
I know this is a bit of an older video, but it helped me out today! I have a report that I need to always show the previous 365 days. So, for instance, today is Oct 31, 2023, so I need 11/1/22 through 10/31/23. I was using Relative Date to filter the last year, but Power BI defaults to UTC. In the last 5 hours of the day, it shows errors because it's trying to show based off of 11/1/23 because it's already tomorrow in UTC. This video helped me figure out how to overcome that with only a minor change to your code! I spent about an hour trying to figure it out, but for some reason a calculated column like this didn't occur to me. Great stuff, as always! I love learning from you fellas.
Like the idea, though I think using "ABS ( DATEDIFF ( ... ) )
Awesome.
Great tip! Thanks @Arklur
You need to look at the difference between datediff, sameperiodlastyear, etc. Sometimes it’s easier to use a calculated column to identify a Latest Date, then select those values and do a datediff, sometimes it’s not - depends on the actual metric, etc.
Thank you son much. I did it with DATEDIFF([date 1];[date2] ; DAY)
Hey Patrick,
The "in Generate series"... was the ace in the hole. Keep going.
Peace
Hi Patrick! Perfect solution when you want to see data relative to some "fixed" date (max order date) that be refreshed at the same time as calculated columns but... when you need to change the date on the report, you need a measure that recalculates on the fly. For example a slicer to select Jan or Feb and the visualization shows 24M relative to selected month. In this cases I use a second "calendar" for the slicer and measures for the last n monts to fliter the visualizations. It would be great if you make a video about this. It's difficult to business developers to understand that calculated columns are not "dynamic" as measures. Thanks!!!!
This is so important…would love to see a video on this. My company intensively uses this technique to allow users to select a date and see trailing 12 months, but I’m not sure we are doing this in the most efficient manner.
Hey Paula! Are you able to explain how you did this in more detail?
I have the exact same situation where I want my end user to select a date and see 2 weeks prior and after that date - this will allow them to select a 'go live' date for product code changes and see how sales were affected before and after the change
@@LaurenTaylorCakeDecorator Hi Lauren! If you want to DM me, I can show you how we handle it. I’m just not sure we do it in the most efficient manner. We employ the same approach that Paula describes above. Happy to jump on a call to show you.
This is a good tip in the power of having a custom dates table. I'm still manually flipping dates over each month in a few reports because I need to show X months in the past and X months into the future, it's not always the same amount on either side. I have done stuff like this before so this video is a good reminder to "be efficient". Seems like it would be a good feature request for the relative date filter.
It exists here if anyone wants to vote for it: ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/33213322--relative-date-filtering-should-have-an-and-or-op
Thank YOU Patrick!!!!!! This is an awesome guide! I spend a whole day searching on how I can dynamically filter cards to the latest reporting month!
I use a similar technique for rolling x weeks as a calculated column on the calendar table. In some of my reports, the "x" in rolling x weeks can be set by the user using a slider. They love it.
Very cool! Thanks for sharing that. 👊
What’s the DAX you use for the slider logic? Just a var that the slider sets for the Datediff?
That sounds like exactly what I'm trying to do, are you able to share your solution?
Been using this concept for a year or two now, it's a godsend.
I may be late to the party, but this saved me today. Thank you, and please keep up the good work!
Hey Patrick, you always hace the asnwers to whatever problem I encounter on Power Bi, you guys are awesome. Thanks, Andy, Argentina.
This was great! I was struggling to get my data to show my a full 2 months back and current month. The relative date filter would only do from like the 28th of furthest back month to now but this completely fixed that! Thank you!!!
Thank you! I was able to implement this for historical weather temps and forecasted temps. My intervals were in days. Great solution!
Woot! Love it 👊
Guyyyy! This video is A A A M M A A Z Z I I N N G G! WOWWW! Simpler things are always the best ones! Thank you so much for bringing it to us simply mortals!
haha thanks Jimmy! much appreciated. 👊
Funny I did similar workaround for customer last year that did similar approach. They wanted data filtered to when last rows were updated. Relative didn't work because weekends and long weekends/holidays data didn't update. This status column works a treat!
Wow Patrick! You're real guru!
Hi Patrick. I am new to Power BI. Thanks for this video!
Not lazy, just efficient... Love it!!
This is a solution I didn't realise I needed but will be so useful!
Great to hear! 👊
I love this solution. I can think of a ton of different use cases for this. You guys are the best 👍
This use of a "flag" is very powerful and useful. Say, you need to only consider the data that complies with several conditions (is higher than.... has more than 2 of ....). One can create variables or a table and SUMX the multiplication of these flags (0s and 1s), only the ones with all 1s will get added.
Thanks for your great Video ,I'm looking for a solution, which allows user to choose this interval .For example user can select that the chart shows last 3 or 6 or 9 Months ,can it be implemented dynamically?
Awesome dynamic tip for working with dates!
Glad you liked it! Thanks for watching 👊
you guys are simply awesome..and most talented.
Really appreciate that! Thanks for watching 👊
Thank you Patrick 😊 this helped me in solving my report problem.
Hi, I am looking for similar solution. But I want to show last 12 months value in a column chart which follow the filter that a user selects. For instance if a user selects Mar 2020, then the graph should shows data from Apr 2019 to Mar 2020.
Great video, a pretty elegant solution. Thanks for sharing!
Amazing, I love it. Please keep doing more videos
AWESOME!!! I have been trying to figure out this same issue. Thank you SO much for this video!
absolutely a quick tip, love it
Thanks. Great video Patrick. I like a lot your videos. Both your's and Adam's.
Appreciate that Jose! Thanks for watching 👊
This is awesome Patrick! Thanks for sharing it - Can we do this dynamic ? Like Summary Page with Month & if we select May 2021 as Month value and drill through to detail - can we show this to render Prior 12 Month data on detail grid ? Is it possible ?
Amazingly simple solution. I used another solution, which has some drawbacks.
Now I have to consider change my report with the other solution ...
Great solution. We do a lot of rolling calculations in our organization so I'm sure this will come in handy. Quick question - how do you keep all of your DAX functions straight? There are so many useful functions that I'm sure are under utilized.
Just used this for one of my projects. Great! I had been puzzling over this for some time.. so I'm "chuffed to bits" (excuse northern English)!!
Wow!! This is an incredible video. I want to do something similar for Weeks, but I also have a time element. E.g. My week would start Monday 6 am until the following Monday 5:59 am. Would really appreciate any assistance.
Very cool trick, Patrick!
you are a life saver
Genius you are. Let me try in projects.
Why over complicate it Patrick? Just convert your date diff result into an absolute value and do a
Hey Patrick, very helpful. Tks for sharing. Question: how to compare month to day versus exactly same days in previous month excluding non working days and public holidays.
Great video! Thank you Patrick!
Great solution - how could you use it to look back to the prior calendar month? Is there a DAX function for that?
I had something similar with quarters. Client wanted to see created tickets in last completed period. It's easy with last calendar week, month, year but there is no quarter. So I added calculated column that calculates relative quarter (0 is current, -1 is previous, -2 and so on). On the beginning I thought that my approach is a approach of a junior person, now I'm more confident that this is a quite good solution :) #confidenceBoost ;)
May the force be with you
Awesome hack.. thank you.. is there a tweak you would suggest to get the last 4 weeks ending Saturday, based on today’s date. Even if today’s date is Wednesday. Plus this is a static column on the fact table. it there a way to make it dynamic based on date or month selection
Hi Patrick, it's great and I've adopted it in most of my BI reports now. Thanks!!!
Btw, would like to know if similar logic could be used at the Power Query of Excel, coz there's no generateseries there.
Your help is much appreciated.
Thanks, Raymond
Nice idea
How about if you want to dynamically choose what quarter/month you want to look at and display latest N quarter/month from the selected quarter/month?
Hi Patrick, Your videos are great and helpful. Is there a way to get the date to automatically pull the max date without having to get in and change the date slicer everyday?
Amazing video, Patrick! Can we apply this to the slicers in the report? I mean can we do exactly what you did but have the var _maxdate to be subject to a date slicer for example? I have tried but no success yet.
Thanks and keep the awesome work. cheers from Brasil!
I started browsing comments to see just this. Looks like i need to wait more for an answer.
@@niru9048 in the end I have managed this in the DB directly instead of using Dax. But I read sometime ago, after I have already worked this out, about the SELECTEDVALUE function to do something similar to what you are looking for. Just came to my mind now but I don't fully remember the details of the solution I'm sorry.
@@marquesman87 in db? That sounds interesting. User selects the slicer and you get say upto 12 months data from that date? I wanted something like this. Actually my date column slicer comes from a dimension table. This same column is to be used as axis on a graph which is amongst many other visuals on page. If i select a date on slicer it will show the graph just for that date. I can turn off the interaction then i won't get values till that day... Rather i will get values for all dates in that dimension date column. Stuck there.
@@niru9048 this is exactly what I'm looking to do, were you guys able to figure this out?
Brilliant work - keep it up
I believe Microsoft was expecting end users will be using filter pane heavily themselves. Therefore they introduced those relative filtering options that are completely useless.
Hi Patrick,
How can I connect this solution with my date filter to keep it Dynamic? based on the selected month it should provide me with - 12 month and + 12 months.
A parameter value might work, but I am curious for this as well and I cannot use parameter due to live connection with AAS model. The solution in this video is great for cases where dates are relative to today or to a max date in a table, but it gets a lot more complicated when you need it relative to a date that is chosen by the user in the report.
Hi, thanks so much, really helped
This is amazing!!!!
That was badassed dude!
I have a question for you that is driving me crazy. I am fairly new to PowerBi and I cannot find the solution to my question ANYWHERE!
In one table I have the following columns; Job ID, YYYY-WW, Hours worked. I want to use a date table to help to consolidate this data. The source only has the data YYYYWW. I split and merged to get YYYY-WW but that's where my knowledge ends. I don't know what to search for for guidance. I want to be able to display graphs that shows hours worked each month by Job ID.
Many thanks all!
Hi! Quick question. Lets say we want to do this, but adding and subtracting months from Today(). How could that be done? I am aware there might be other solutions, like PrevYear = date(year(now))-1, month(now()), day(now())) and same for the following year. It's more a question of How do I substract or add years, months or dates to the Today() formula. Or is not meant to be used like that?
Thank you for all the videos Patrick, they are my main source of PBI inspiration. Also, is there a way to count the number of days between two dates, excluding weekends. I'm really stuck and I fear my only way out is to head over to your laptop.
This is awesome! Thank you!
Thanks!
Hi Patrick, great video, thanks! How can this technique be applied if we have various regions and each region has its own max date? Your solution gets the max of the region with the freshest data as a reference. But I need regions with older data to show the last 12 months according to their max date. I would be grateful if you just cast your shadow on this riddle.
Hi Patrick, that was a great video,
I have a different scenario and that is, i need last 12 months average sales please let me know how do I do it....
Thanks Patrick this is awesome, but is there a way for the slicer to default to show previous 12 months but still allow the end user to go back farther by sliding it back to a later date?
Great video! Is it possible to use this with weeks? I have a report where I want a relative date filter with the last 4 weeks MONDAY-SUNDAY. The calendar weeks are Sunday-Saturday, and if I take last 4 weeks it will of course be relative to today.
Hi Patrick. I am new here...great videos!
If you don't mind me asking, what's the name of the screen recorder that you use in your videos?
Hi Patrick, thank you for the great video, however, I have a question. I only using 3 month period from my calendar, so using this DAX, is it going to be a 3 month rolling period so it automatically updates based on current month? Thanks.
I love your videos! Do you know or have you created a video that can help us combine two different date tables? For example a creation date and login date. How could combine those two tables to display two different information points about a user?
I have a problem with this dataset and need help. The data stored to date figure where each employee get paid differently. A group of people who get paid monthly, the other gets paid weekly. The weekly can fall into 2 weeks or more than 7 days. I want to get a summary and balance of the figure. However, in a column chart, it quadruples the figure as many ppl get weekly payment.
I would love some help on how to write a calculated column to compare if the paydate is the latest day of each month. Bear in mind that Endofmonth wouldn't be a case for each pay period. It could be on the day 27, 14 or 31 etc. Could you cover a video with row evaluation? Thanks you in advance.
Nice video!!! But how shall I do if I want to get rid of set of dates? I mean get rid of dates from a period to another?
Hey Patrick, love the videos, keep them coming!!...
I currently have a similar problem that I am struggling with which I partially solved using similar logic. However, I have a need to add a business unit dimension. Using your example, I would want to see past 12 month historical and 12 month forecast sales per business unit (filtered by a slicer) as well as in aggregate (no business unit selected). Each business unit has greatly different "max date" for their sales. Any ideas?..
Have you found solution to it? I am not sure if the calculated column added to Calendar table can change its Max value depending on slicer selection of Business Unit. I am struggling with the same problem.
@@gondebas me too
Hey Patrick, thank you for all your awesome videos... I just have a question for you. Here is my scenario:
1. I'm using PowerBI Embedded
2. We store all our date/time data in UTC.
3. Any user seeing our embedded reports should have the UTC date-times converted to its local date-time. (The only way I imagine this is possible is by using a filter or slicer and passing that user local time offset to the report itself.
4. The model has a relationship between a Calendar Table and the current UTC date-time column, we have a date-time slicer to dynamically change the line chart visual that we have.
5. I tried to use calculated columns (it didn't work as we cannot use slicer selected values on those), and also tried with a measure but it simple is out of scope.
Do you think is there a way to achieve what I need?
Thank you!.
thank you! you saved my day & report.
Love it! Glad it was useful 👊
Great videos thanks! Is there a way, instead of both dates being relative, the first one is static and the other moves by one day every day? For example, I want a report that looks at the data from the 25th of every month through yesterday. Up until the 24th and then it would reset.
Great video, is it possible to drill up/down when this method?
Question: What about Min Date???? like dates between ??? i want to see data of current month from 10th to 20th as well as same period for previous month. please help
Hi Patrick, thx a lot.
Can you show me how to calculate the number of days between two date (as a number, not a date).
Thank you in advance!
Great solution, thanks ! Is there a way to make the same but with Quarters for example? I tried changing the value "MONTH" in the datedif formula to "Quarter", but the formula uses calendar quarters in that case instead of using dynamic quarters, for example if I am in july my dynamic actual quarter would be from may to july, but the function uses the calendar quarters ( jan to march, apr to june, july to sept, oct to dec).
OMG. Wie cool ist das!
Hello Patrick! This video is great! The only piece I can't figure out is how you sorted the month/year to start on March 2018 and continue to March 2020 - is there a secret? My months are all jumbled up and when I sort by Month#, it puts Jan and Feb before March.. any help is appreciated!
Hi! But what if we want to keep the last/newest date selected in the relative date filter? So we don't want to see the data of the hole periode of the relative date but just today and then the possibility to select the other days of the relative dates as well?
Hey man,
So nice.that what you do there ..
I have a doubt, in India. We follow the fiscal year from Apr-mar
Now for example I want to define a value in a column based on docdate .. which should give a result that FY19-20 for the date range of Apr-19 to Mar-20
How do I do it!
Hi Patrick: I am looking to create a Selection Date which is always selected on As-On Date, and we need a Trended Graph which will show past 10 Days Trend based on the As on date selected ..Also the same As -on date should filter My KPI for that particular date.
....Share your thoughts on same..
In this same visual or Line visual, do you know a way to show, for every month of the year, the total sum of Actual up to yesterday + the total sum of the budget from today up to the end of year? In these case, you would have the same data model as the one you show in this video.
can you do last 10 weeks, last 12 weeks etc in DAX?
I have a date slicer which should show last 12 months as selected when report opens. Further, user should have flexibility to select any other date range. Please any one answer this question
hey Patrick, that's nice, i just want check with you, based on a selection of multiple dates in the slicer, i need to calculate the min and max - only from selected dates in a slicer, is there a way to store the selected date values into an another measure/variable and pass on to the column, it would be great help thanks in advance.
Thanks for this video. I have one date and time column in my dataset and i want to show the 48 hours trend. so, could you please help me.
HI , how we cant find total sales 3,4,5..20 days after or before from selected day?Tahnks.
Hi Patrick,
How do I filter data between this period which looks something like this
(minimum date from column) to (maximum date from column minus 30 days)
great, I have a different requirement of achieving count rows . I have table request received data, request completed date and I need show many request received on each days and completed on each day.(I need to apply country/team as filter to apply) let me know if you have any video on how to do this
Hi Patrick!
Great video as always! This is an awesome tip. Thanks for sharing it.
At the same time, I would like to ask you about some basic DAX stuff that I have been unable to solve so far.
I have the following DAX script to be used in Power BI platform.
Sales by Year and Color Filter by Color and Year =
SUMMARIZECOLUMNS (
'Date'[Calendar_Year],
'Product'[Color],
"Avg_Sales", CALCULATE (
AVERAGE (Sales[Quantity]),
FILTER ( 'Product','Product'[Color]="Black" ),
FILTER ( 'Date', 'Date'[Calendar_Year_Number] >= 2005 ) )
)
It is all ok so far but I need to extend
FILTER ( 'Product','Product'[Color]="Black" )
to include “Black”, “Blue” and “Orange”, to be considered in the same filter. Something like:
FILTER ( 'Product','Product'[Color]="Black" OR "Blue" OR "Orange")
Any help?
You could use: Product[Color] IN {"Black", "Blue", "Orange"}
Hi, Akhil :
You solved my problem man.
It is just what I have been looking for.
This is a very important matter to know because it let you manage groups in a very efficient and easy way.
I have been looking around for this situation to be solved in many websites and articles, but I have gotten nothing till now, of course. All that I got was just the usual example considering only one component to be the filter.
Thanks a lot!
My very best regards to you!
AWESOME!!!!! THANK YOU
How did you did the “projected sales” measure?
What is the reason for naming variable start with _
How to make a filter which takes a date as a filter and shows 15-day range from today when I open it and can change the date also to anything can u help in this
Hi Patrick!
I just partially solve my problems just applying your idea here. I made a binary variable that selects the colors as “1” and keeps the rest of the colors as “0”.
Anyway, I keep my question because it could be a lot of better and short if we can manage this filter situation in just the same coding here:
FILTER (
'Product','Product'[Color]="Black" OR "Blue" OR "Orange")
Any help?
Hello
please I have a question .
i calculated a value by measure and I want to display automatically this value in the next month, how can i do that ? i tried nextmonth fucntion but didn't work with me, would you help me please ? for example : I calculated a value in the first month of the year, and I want to show this value in the second month and so on
Hi, I have a doubt. In one of my project at the time of while launching the report it will show the last 6 months of data after that it will show based on the selection. How can I achieve this. Kindly help
I have population projection by year data between 2011 and 2050 where year's format is text. How can I get relative date to do 10 year projection from the selected year in the slicer. Thank you. Siva