Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Relative Date Filters for Last Days

I looked for some information on this but was not able to find anything and in an effort to save some time I figured I would ask here. I am working on a report where the source data is updated daily and I have a date table that contains every date in the year. 

 

I want to use a realtive filter to show the prior days data however when I went in and created the filter visual the "current day is not correct".

 

My System date (from my tool bar) is 3/25/2020 however if I enter Last 1 Days in the realtive filter it is displaying 3/26/2020 which is causing blank graphs as the data does not exist yet. I can change this to show the prior 3 days which would actually give me what I want which is the prior 1 day but I feel like there is an actually solution to this. 

 

Need Help
  • edhans's avatar
    edhans
    6 years ago

    I've run into enough minor or major issues with the relative date filtering, I've abandoned. it. I add a field to my Date Table in Power Query to give me a true or false. In your case, it would be:

     

    = if Date.AddDays(
    DateTime.Date(
        DateTime.LocalNow()
        )
        ,-1) = [Date]
then true else false

     

    Paste this entire section into a new Blank Query in Power Query in the Advanced editor and you'll see it. It will only show true for yesterday.

     

    let
    Source = {Number.From(#date(2020,3,1))..Number.From(#date(2020,4,30))},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Date.AddDays(
    DateTime.Date(
        DateTime.LocalNow()
        )
        ,-1) = [Date]
then true else false)
in
    #"Added Custom"

     

     Only yesterday should show TRUE. Drop that in your filter and every refresh, it will update.

     

4 Replies

  • v-lionel-msft's avatar
    v-lionel-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    Set the [Date Range] option:

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • edhans's avatar
    edhans
    Icon for Community Champion rankCommunity Champion
    6 years ago

    I've run into enough minor or major issues with the relative date filtering, I've abandoned. it. I add a field to my Date Table in Power Query to give me a true or false. In your case, it would be:

     

    = if Date.AddDays(
    DateTime.Date(
        DateTime.LocalNow()
        )
        ,-1) = [Date]
then true else false

     

    Paste this entire section into a new Blank Query in Power Query in the Advanced editor and you'll see it. It will only show true for yesterday.

     

    let
    Source = {Number.From(#date(2020,3,1))..Number.From(#date(2020,4,30))},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Date.AddDays(
    DateTime.Date(
        DateTime.LocalNow()
        )
        ,-1) = [Date]
then true else false)
in
    #"Added Custom"

     

     Only yesterday should show TRUE. Drop that in your filter and every refresh, it will update.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      So I am new to DAX and M but I follow what you are suggesting. For the M code chunk would I ever need to change

      Source = {Number.From(#date(2020,3,1))..Number.From(#date(2020,4,30))}

       

      • edhans's avatar
        edhans
        Icon for Community Champion rankCommunity Champion
        6 years ago

        Yes. That was just an arbitrary date range of March 1 through April 30 I used for this example. You can put in whatever date range you want, or better yet, make this date range fully dynamic so it moves along with the dates added to your model. See this article for instructions on that: Creating a Dynamic Date Table in Power Query


        That is how I do all of my date tables in Power BI.