Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @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.
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingSo 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingUser | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |