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 yesterday's sales performance.

12 Replies

  • austinsense's avatar
    austinsense
    Icon for Impactful Individual rankImpactful Individual
    10 years ago

    You could create calculated columns on the date table ...

     

    TODAY FLAG = IF( Date[Date] = NOW(),1,0)
YESTERDAY FLAG = IF( Date[Date] = NOW()-1,1,0)

    and then use those as filters on the page or visualization.

     

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

      A [Days from Today] column in a Date Dimension works especially well in Power BI.  You can DATEDIFF against today to allow users to filter on a rolling range of dates.

    • successken's avatar
      successken
      Regular Visitor
      10 years ago

      Thank you. I took your idea and applied it in Power Query with 

       

      if Date.IsInCurrentDay([Date]) then "Today"

      else if Date.IsInPreviousDay([Date]) then "Yesterday"

      else ""

       

       

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