Power BI: Use DAX to get relative dates

[@bcwest619]

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.

[@Galahadoc]

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.

[@Galahadoc]

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

[@awalpen]

Hey Patrick, you always hace the asnwers to whatever problem I encounter on Power Bi, you guys are awesome. Thanks, Andy, Argentina.

[@paulasturla6541]

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!!!!

[@chadkilpatrick4864]

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.

[@LaurenTaylorCakeDecorator]

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

[@chadkilpatrick4864]

@@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.

[@Soul1Slayer8]

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!!!

[@fcoatis]

Hey Patrick, The "in Generate series"... was the ace in the hole. Keep going. Peace

[@spannclint]

I may be late to the party, but this saved me today. Thank you, and please keep up the good work!

[@JorgeSegarra]

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!

[@arklur3193]

Like the idea, though I think using "ABS ( DATEDIFF ( ... ) )

[@GuyInACube]

Awesome.

[@jimmyni1983]

Great tip! Thanks @Arklur

[@cybertrophic]

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.

[@Did-nm6fo]

Thank you son much. I did it with DATEDIFF([date 1];[date2] ; DAY)

[@dollisonjoseph]

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!

[@jeremyfirth]

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.

[@GuyInACube]

Very cool! Thanks for sharing that. 👊

[@cybertrophic]

What’s the DAX you use for the slider logic? Just a var that the slider sets for the Datediff?

[@hatenk]

That sounds like exactly what I'm trying to do, are you able to share your solution?

[@quinnmichael2657]

Been using this concept for a year or two now, it's a godsend.

[@mahsasahargahi7118]

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?

[@ayltonasc28]

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.

[@lisarand6314]

Hi Patrick. I am new to Power BI. Thanks for this video!

[@tylerbuckingham8639]

Thank you! I was able to implement this for historical weather temps and forecasted temps. My intervals were in days. Great solution!

[@GuyInACube]

Woot! Love it 👊

[@himoray]

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

[@jimmyni1983]

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!

[@GuyInACube]

haha thanks Jimmy! much appreciated. 👊

[@rauljimenez5485]

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.

[@deejohn064]

Great solution - how could you use it to look back to the prior calendar month? Is there a DAX function for that?

[@crackerzin-bi6746]

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 ?

[@YoutubeUsserr]

Thank you Patrick 😊 this helped me in solving my report problem.

[@Kiara_Wrestler]

Not lazy, just efficient... Love it!!

[@pankajnain8184]

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.

[@DarkSideOfDawn]

This is a solution I didn't realise I needed but will be so useful!

[@GuyInACube]

Great to hear! 👊

[@ashwinshankar]

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

[@mattmiller07]

I love this solution. I can think of a ton of different use cases for this. You guys are the best 👍

[@shujashakir9952]

Wow Patrick! You're real guru!

[@GripHappens]

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!

[@tracey-leefebruary4567]

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.

[@wvajones]

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?

[@yiuchunleung4644]

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?

[@nikhargesumit9069]

you guys are simply awesome..and most talented.

[@GuyInACube]

Really appreciate that! Thanks for watching 👊

[@aiandahkeem5385]

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?

[@anilbyroe7268]

Great video, is it possible to drill up/down when this method?

[@marquesman87]

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!

[@niru9048]

I started browsing comments to see just this. Looks like i need to wait more for an answer.

[@marquesman87]

@@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.

[@niru9048]

@@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.

[@hatenk]

@@niru9048 this is exactly what I'm looking to do, were you guys able to figure this out?

[@tanyakonono9074]

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?

[@DPFierce]

Awesome dynamic tip for working with dates!

[@GuyInACube]

Glad you liked it! Thanks for watching 👊

[@hichamas4]

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.

[@martingoedhart2294]

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.

[@vladimirpino5931]

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.

[@PB-vx4nc]

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..

[@nazimscr]

Amazing, I love it. Please keep doing more videos

[@amandaharris8991]

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?

[@lifeisgreat11]

How did you did the “projected sales” measure?

[@JoshYeats]

Great video, a pretty elegant solution. Thanks for sharing!

[@AshikKhan]

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....

[@narendrareddy8420]

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)

[@pankajkumar-oj8mr]

Hi Patrick, I have a campaign dashboard and every campaign has start and end date. How we can get the data within each campaign??

[@Did-nm6fo]

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!

[@thevenom6902]

HI , how we cant find total sales 3,4,5..20 days after or before from selected day?Tahnks.

[@varishagokoel311]

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?

[@92huylang]

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.

[@sushicatsan]

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.

[@sherryizzie5309]

Very cool trick, Patrick!

[@UrszulaPukalska]

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 ;)

[@latheshnaik3787]

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.

[@traceysorenson8027]

AWESOME!!! I have been trying to figure out this same issue. Thank you SO much for this video!

[@andreadacampo9224]

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?

[@Anuragkumar-wx8ml]

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

[@sureshramesh3471]

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

[@kishoreyc]

Genius you are. Let me try in projects.

[@jbgonzalez13]

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.

[@jbgonzalez13]

Thanks. Great video Patrick. I like a lot your videos. Both your's and Adam's.

[@GuyInACube]

Appreciate that Jose! Thanks for watching 👊

[@anamicula5480]

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!

[@rlei327]

absolutely a quick tip, love it

[@teob7947]

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).

[@gondebas]

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.

[@jaimemagana]

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?

[@HeliSal700]

Amazingly simple solution. I used another solution, which has some drawbacks. Now I have to consider change my report with the other solution ...

[@nida476]

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

[@jhazielpetersen761]

can you do last 10 weeks, last 12 weeks etc in DAX?

[@govindarajann7295]

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

[@edmistonphotography1872]

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.

[@prashantkushwahavlogs]

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.

[@razmilea9075]

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.

[@hamzamottali3567]

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

[@mjfigg3050]

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?..

[@gondebas]

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.

[@bliksimstraal]

@@gondebas me too

[@sivamagesh]

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

[@SoNJFF]

Hi, thanks so much, really helped

[@vinayakbhat999]

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

[@googita416]

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.

[@EricaDyson]

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)!!

[@frankgovers8420]

May the force be with you

[@sridhar712]

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!

[@TheAusafonly]

What is the reason for naming variable start with _

[@ThalassaMavri]

Great video! Thank you Patrick!

[@sakshirawat1328]

Hey, hope you are doing wonderful!! I am working on a data set and I want to show latest data in tiles. Now I want this data to be displayed whenever user opens the dashboard. Also what I need is that user to be able to filter out data based on the dates present in date column. I tried using 'Top N' on Report Level filter but that isn't helpful. What can be done here?

[@GuyInACube]

From this comment, I am not clear what you are trying to do. It may be better to post this comment over on community.powerbi.com with a screenshot.

[@dheitsc7871]

Hey Patrick, I just watched this and other videos regarding this topic but I could not find a good solution for the following problem and I wonder if anyone else might find a solution. Lets say I have 2 different tables like Tickets and Events, both have a creation datetime and they have an active relation where the Event contains the Ticket ID they belong to. I have a report where I want users to choose the report date they view because they might see the current data but also the data 12 months ago i.e.. The problem is that I cannot create an active relation between the dates because there is already one active and so far I used synced slicers for the creation date fields so that a user only needs to change one date. In one visual I want to see the selected month only, but in another visual I want to see the selected month + 6 months in the past. (I archived this by using Top N function and set only an End Date in the slicer, no start date) But in the future I need to use filters instead of slicers because I would need to use URL encoded filters for integration purposes. I then thought I could easily create a calculated column like a "is displayed" field which is using the selectedvalue function to retrieve the current filtered date but the filter context is not available at the column level. Is this somehow possible? Do you have any idea?

[@FernandoWartchow]

Nice idea

[@diegool88]

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!.

[@atiqaraj8582]

Hello, how I can show overlap dates in matrix ?

[@absolutedisgrace]

Why over complicate it Patrick? Just convert your date diff result into an absolute value and do a

[@hectoralvarorojas1918]

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?

[@danmenez]

That was badassed dude!

[@PaulieM]

Brilliant work - keep it up

[@mariokaram7640]

i am having problem , filtering by month and year at the same time ,like the 12 month of 2018 and 12 month of 2019

[@GuyInACube]

We would need more details to understand the problem. This with the relative date slicer?

[@mariokaram7640]

Guy in a Cube im sorry I didn’t explain well my situation, on the other hand i managed to fix my issue ! Appreciate your support

[@gdtab6177]

Thanks!

[@1yyymmmddd]

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.

[@davidbrown1373]

This is awesome! Thank you!

[@martinsch745]

thank you! you saved my day & report.

[@GuyInACube]

Love it! Glad it was useful 👊