cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
yennhi95zz
Frequent Visitor

Dynamic Custom Date Column with DAX

Hi,

 

In the date slicer, From DATE_1 to DATE_2, I want to fix DATE_2 as Today(), the DATE_1 can be customized. I am thinking of adding a new column using DAX by going into Modelling > New Column > DAX:

 

Slicer Date = IF(Dates[Date] <= TODAY(),"Until Today","None")
 
Then filter all pages with "Until Today".
The point is, when it comes to the next day, the DATE_2 does not change. How to make it dynamic?
 Today is 07 Sep and it is showing yesterday (06 Sep 21):
yennhi95zz_0-1630974790017.png

 

Hope to receive help from you all.

 
3 REPLIES 3
AllisonKennedy
Super User
Super User

@yennhi95zz  You should be able to do this without a calculated column, but the difference could be due to timezone. What timezone are you in? 

 

Here's the starting how to for the TODAY function https://dax.guide/today/ that will guide you to lots of fantastic resources from SQLBI on handling dates in Power BI. 

 

If you do need to add a column, I recommend adding it in Power Query using M, and convert to UTC time to make the filters work in Power BI service. 

 

https://www.thepoweruser.com/2019/10/21/handling-different-time-zones-in-power-bi-power-query/ 


Please @mention me in your reply if you want a response.

Check out my Tokyo 2020 report with live stats - KUDOS much appreciated

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Hihi, thank you for your reply.

I don't get it much. Can you guide more specifically?

 

Thank you in advance 😄

If the timezone isn't the issue, just:

1) Select the slicer you're pointing to in your screenshot.

2) In the 'Filters on this visual' section, change date slicer to 'Relative date' (it might be set to 'Advanced' or 'Basic')

3) Set to In the last 10 years and be sure to tick 'Include today'

 

This should do the same thing without needing to create a calculated column and will update unlike the calculated column which will only update if you refilter the slicer.


Please @mention me in your reply if you want a response.

Check out my Tokyo 2020 report with live stats - KUDOS much appreciated

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!