Forum Discussion

yennhi95zz's avatar
yennhi95zz
Frequent Visitor
4 years ago

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

 

Hope to receive help from you all.

 
Need Help

3 Replies

  • AllisonKennedy's avatar
    AllisonKennedy
    Community Champion
    4 years ago

    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/ 

    • yennhi95zz's avatar
      yennhi95zz
      Frequent Visitor
      4 years ago

      Hihi, thank you for your reply.

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

       

      Thank you in advance 😄

      • AllisonKennedy's avatar
        AllisonKennedy
        Community Champion
        4 years ago

        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.