Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Is Today Column

Hi,

 

I am trying to create a column called Is Today and it isn't pulling through the correct answer on the if query:

 

Is Today = if([date]=Today(), "Yes", "No")

 

It is only pulling through the result as No even though there are multiple dates which match todays date.

 

Any help would be appreciated.

Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    hi   Anonymous 

    For your case, that must be that your [Date] column has the different "time" in the same date. or it is a datetime column in edit queries but you just change the datatype in power bi.

    For example:

    So just use DATE Function to nested in the formula as below:

    https://docs.microsoft.com/en-us/dax/date-function-dax

    New Is Today = if(DATE(YEAR([date]), MONTH([date]), DAY([date])) = Today(), "Yes", "No")

    Result:

     

    Regards,

    Lin

9 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    6 years ago

    You should use calculated column in Data view.

     

    DAX has the same syntex as Excel for creating today's date.

     

    =TODAY()

     

    for the Query Editor, Create a new column ans use the below syntax

     

    DateTime.LocalNow()

     

    or try with Now() function.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      I have created a new column called 'Todays Date', using the belo formula:

       
      Todays Date = NOW()
       
      Then I have used the below formula for the IsToday column:

       

      IsToday = IF(Query2[new_ResolvedOn]=Query2[Todays Date], 1, 0)
       
      I have tried reformatting the resolved on and todays date formats so they are identical dd/mm/yyyy but it still doesn't work despite them matching.
       
       
  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Your date might have timestamp

    Is Today = if([date].date=Today(), "Yes", "No")

    use .date

    Appreciate your Kudos.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi,

       

      Thanks for the response, it does not seem to like that:

       

      • MartynRamsden's avatar
        MartynRamsden
        Solution Sage
        6 years ago

        Hi Anonymous 

         

        Try this:

        Is Today = IF( Query2[new_ResolvedOn] = TRUNC( TODAY() ), "Yes", "No" )

         

        Best regards,
        Martyn

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    6 years ago

    hi   Anonymous 

    For your case, that must be that your [Date] column has the different "time" in the same date. or it is a datetime column in edit queries but you just change the datatype in power bi.

    For example:

    So just use DATE Function to nested in the formula as below:

    https://docs.microsoft.com/en-us/dax/date-function-dax

    New Is Today = if(DATE(YEAR([date]), MONTH([date]), DAY([date])) = Today(), "Yes", "No")

    Result:

     

    Regards,

    Lin