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

    • elliotdixon's avatar
      elliotdixon
      Icon for Responsive Resident rankResponsive Resident
      10 years ago

      Hi karthik have you heard of any updates / fixes around this? Still causing me lots of issues being in New Zealand and +13 hours.

       

      ED

      • Phil_Seamark's avatar
        Phil_Seamark
        Icon for Microsoft Employee rankMicrosoft Employee
        10 years ago

        Just type in "tomorrow" instead ;)

         

        (also in NZ)