Forum Discussion

FlowViz's avatar
FlowViz
Icon for Helper III rankHelper III
5 years ago
Solved

Dynamic date range in OData query

Hey Guys,

 

I have a query for my dataset that ends in "and ChangedDate ge 2020-02-01Z" I always want it to pull the last 6 months of data from the time of load/refresh. At the minute I'm having to manually go in and change this - I wondered if there was a way I could do this dynamically where it will just always be the last 6 months? 

Need Help
  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    Hi FlowViz 

     

    ponnusamy 's expression has an extra ')' at the end. Remove that and you will get the correct result. There should be only a single ')' there.

     

    Date.ToText(Date.AddMonths(Date.From(DateTime.LocalNow()),-6),"yyyy-MM-dd")

     

    Regards,
    Community Support Team _ Jing

6 Replies

  • ponnusamy's avatar
    ponnusamy
    Icon for Solution Supplier rankSolution Supplier
    5 years ago

    FlowViz 

     

    Try this 'and ChangedDate ge "Date.ToText(Date.AddMonths(Date.From(DateTime.LocalNow()),-6),"yyyy-MM-dd"))" in advance editor

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    5 years ago

    That expression from ponnusamy should work.  Does your query look like this?

     

    "... ChangedDate ge " & Date.ToText(Date.AddMonths(Date.From(DateTime.LocalNow()),-6),"yyyy-MM-dd")) & "Z"

     

    Pat

    • v-jingzhang's avatar
      v-jingzhang
      Icon for Community Support rankCommunity Support
      5 years ago

      Hi FlowViz 

       

      ponnusamy 's expression has an extra ')' at the end. Remove that and you will get the correct result. There should be only a single ')' there.

       

      Date.ToText(Date.AddMonths(Date.From(DateTime.LocalNow()),-6),"yyyy-MM-dd")

       

      Regards,
      Community Support Team _ Jing