Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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. 

 

3-25-2020 9-57-07 PM.png3-25-2020 9-59-25 PM.png

1 ACCEPTED SOLUTION
edhans
Super User
Super User

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.

2020-03-25 19_41_14-20200318 - Grouping Data to Count - Power Query Editor.png

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

4 REPLIES 4
v-lionel-msft
Community Support
Community Support

Hi @Anonymous ,

 

Set the [Date Range] option:

aaa6.PNG

 

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
Super User
Super User

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.

2020-03-25 19_41_14-20200318 - Grouping Data to Count - Power Query Editor.png

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

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))}

 

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.