Forum Discussion

successken's avatar
successken
Regular Visitor
10 years ago

Today Filter in Reports

I'm not if I'm missing this. Is it possible to create gauge reports and such with a date filter of Today or Yesterday without utilizing the Q&A?   My goal is to have a quick dashboard reference on ...
Elliott's avatar
Elliott
Icon for Advocate II rankAdvocate II
10 years ago

Following on from MrJolly's post, and thanks to smoupre for providing this solution to me orginally..

 

DATEDIFF can be acheived with the following formula:

 

Days Aging = DATEDIFF([Date],TODAY(),DAY)

 

This will give you a new column which will specify the total number of days from the current day, starting from '0' as today.. '1' as yesterday etc.

This gives better functionality in the case that you may want to specify different/longer time periods.

  • successken's avatar
    successken
    Regular Visitor
    10 years ago

    MrJolly and Elliot - thank you for your replies. Your prespective with DATEDIFF has added differnt dynamic that I look forward to try out. I am going to see if I can add this via Power Query.

    • fbrossard's avatar
      fbrossard
      Icon for Kudo Commander rankKudo Commander
      10 years ago

      If you have a "date" table, you could enrich it with some calculated column.

      For example :

      [Relative Date Offset] = INT([Date] - TODAY()) [Relative Date] = IF([Relative Date Offset]=0; "J "; "J " & IF([Relative Date Offset]>0; "+"; "") & [Relative Date Offset]) 
[Relative Month Offset] = ((12 * YEAR([Date])) + MONTH([Date])) - ((12 * YEAR(TODAY())) + MONTH(TODAY())) 
[Relative Month] = IF([Relative Month Offset]=0; "M "; "M " & IF([Relative Month Offset]>0; "+"; "") & [Relative Month Offset]) 
[Relative Week Offset] = INT(([Date] - (TODAY() - WEEKDAY(TODAY();2)+1))/7) 
[Relative Week] = IF([Relative Week Offset]=0; "W "; "W " & IF([Relative Week Offset]>0; "+"; "") & [Relative Week Offset])

      Bla bla bla ...

      You can modify this for your needs. And use them as filter, slicer or just axis in your reports.

      • karthik's avatar
        karthik
        Icon for Advocate I rankAdvocate I
        10 years ago

        Please note that we have had few issues using Today or Now in the Power BI Service as it would return the current date based on the regional/date settings of the data centre where the service is hosted. If you are in a different time zone this would be an issue.