How to set a time frame while defining a data source and reflect that in date slicer?

[sohananahid]

Hi: Good day! I am developing a dashboard to show different maintenance events. I have a data source [a Db table], where events info are stored for last few years. I am trying to filter data with starting date as beginning of this year. So far I have my data source settings with the sql query as below.

1. As we publish this dash board and time progresses: is there a way to dynamically set the time like for last 6 months rather than hard coding the start date?
2. I have a Summary table visual to show the events with start and end dates, etc. There is a slicer with start date range. Even I am pulling data from the DB table with start date as >= 2020-01-01, the slicer does not stop at Jan 01 2020, but shows previous months and years. The data on my table visual is from Jan 01 2020 and onward even if I select a date earlier than that in the slicer. How do I stop the slicer date start from Jan 01 2020?

Many thanks in advance. 🙂 

[amitchandak]

@sohananahid , refer if this can help

https://community.powerbi.com/t5/Desktop/Power-Query-Dynamic-Date/td-p/348222

[mahoneypat]

You can make another query that simply returns today minus six months with this expression (make a blank query and put this formula in the Formula Bar for the Source step.

= Date.AddMonths(Date.From(DateTime.LocalNow()), -6)

If you call that query "datefilter", you can use it in your main query by concatentating it into the SQL string.  Look in the advanced editor for your main query and replace the hard coded date with "datefilter".  Your original SQL will be in quotes, so you'll need to concatenate it in like this

"... WHERE startdate >=" & datefilter & "... rest of the SQL if applicable"

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[sohananahid]

Hi @mahoneypat : Thanks a lot for your response. I followed your steps, but getting this error:

Probably I am missing something! Really would appreciate it if you could point me to right direction. 🙂

[mahoneypat]

You need to make the edit in the Advanced Editor, not the Advanced Options of the SQL connection popup.  In the query editor, on the home tab click on Advanced Editor.  You will see your SQL statement in the Source= step of the query.  It is there that you need to concatenate the datefilter query.

If you get stuck, just copy/paste the text from the Advanced Editor, and I can respond with modified code.

Regards,

Pat

[sohananahid]

Hi @mahoneypat : Thanks for your response. I did try to edit the query in Advance Editor, but still getting an error as PFB:

The code for the source is as

Source = Sql.Database("…", "…", [Query="select * from dbname.natgas.pipeline_maintenance_events#(lf)where startDate >=" & datefilter & ""])

Thanks in advance. 

[mahoneypat]

Can you show the original SQL to see what format the Date was in?  The datefilter query will need to return it in that format?  It is probably YYYY-MM-DD, so you will need this in datefilter instead

= DateTime.ToText(Date.AddMonths(DateTime.LocalNow(), -6), "yyyy-mm-dd")

Also, if the date is last you shoudn't need the &""

Regards,

Pat

[sohananahid]

Hi @mahoneypat : Thanks for your response. The date is in YYYY-MM-DD format, the query returned right date.

But as I write the source sql string as below, I get an error:

    Source = Sql.Database("XYZ", "ABC", [Query="select * from ABC.natgas.pipeline_maintenance_events#(lf)where startDate >=" & datefilter ""])

What am i missing? Thanks in advance. 

[mahoneypat]

Get rid of the "" at the end. Also if you have other lines in your query the Source line needs a comma at the end. If just Source no comma. 

Regards

Pat

[sohananahid]

Hi @mahoneypat Thanks a lot for your continued responses. I am getting this message now.

As I try to run the query, it kinda stuck at showing the messages. Any help is highly appreciated. 

[mahoneypat]

Looks like you are making progress.  You can just hit Run on that.  It is just a "Are you sure?" kind of prompt.  You can turn those off in the settings.

I do see that the date looks incorrect 2020-55-10.  Is that what the datefilter query is returning?  Try it as is, but that looks off.

Regards,

Pat

[sohananahid]

Hi @mahoneypat Thanks for your patience with helping me out! 🙂

The date is off, that's correct. Also, I get an error while tried to apply the query changes

These are at the Power Query Editor:

Do I have to do any more step? Thanks.

[mahoneypat]

I think that date format is returning the minute instead of the month.  Please use this one instead.  It matters that the MM is uppercase.  Please confirm it returns the date six months ago, and try it again.

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

Regards,

Pat

[sohananahid]

Hi @mahoneypat: Thanks for your response. 

The date looks good now [I am trying to get last 1 months data]

But as I try to run the edited data source and “Run” as native query, I am getting the error:

Much appreciated in advance. 🙂

[mahoneypat · Accepted solution]

Sorry this is taking so many interations.  I looked back at your original post and see that the date has single quotes around the date, so those need to be added to the date concatenated onto the SQL statement.  You can do it in the main query, or you can do it in the datefilter query like this

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

I will cross my fingers that this time is the one that works.

Regards,

Pat

[sohananahid]

Hi @mahoneypat  It worked this time! Thanks a lot for your help, really appreciate it! 🙂

[sohananahid]

Hi @mahoneypat : Good day! I have 1 related question to this thread. As I set a 'datefilter' query to get events with starting date >=last 1 month as

= "'" & Date.ToText(Date.AddMonths(Date.From(DateTime.LocalNow()), -1), "yyyy-MM-dd") & "'"

and 

…where startDate >=" &datefilter

It works and returns 380 rows [events] with right starting dates expected. Now as I use the event 'Id's for these events to get event details from an api [event details api], I've a parameter 'Id' and detailsFunction to call the api with the id. there is 1 to 1 relationship on 'id' between these 2 data sources. I was expecting to get 380 rows of event details too for the 2nd table I am building dynamically. But I get 1500 rows with start date before last month. 

What am I missing here? Many thanks in advance.

[mahoneypat]

That is odd.  So is this all in the same query?  Once you return the event ID list, you are invoking your custom function to get Event Details?

Or are you Referencing the first query in the second?  It should works correctly either way, but I don't know if some weird query folding is going on.  If you load just the first part, put the list of events in a static table, and invoke your function, do you get 380 rows or 1500?

Regards,

Pat

[sohananahid]

Hi @mahoneypat:  Good day! Thanks for your response. I have to separate queries for these. First there is a query as we discussed recently for getting the events summary info. Then using the event 'Id's from this table, I dynamically create the second event details table [using a second function/ query] for those 'Id's. 

As a test: I separately make a static table and invoke the funcion [2nd], I got similar no. of rows. So, I deleted the 2nd table and did the last step to invoke the function [the datefilter was copied too] to create the table. Now, the datefilter is associated too with this table. So, I am getting similar no of rows now as I 'Refresh'! So, it's good now! 🙂

There is 1 more issue: I have a slicer 'start date' to show the events in a 'Table' visual. I was expecting this slicer should show only dates from last 1 month from today as I set my 'datefilter' like that to get the data . But I see the slicer shows dates from 2014 as that is in the database table.

Any clue how I can make the slicer to show only dates as set in my 'datefilter'? Many thanks in advance. 🙂

[mahoneypat]

You could use your datefilter query also to make your Date/Calendar table.  For example, =List.Dates(datefilter, Duration.TotalDays(Date.From(DateTime.LocalNow())-datefilter, #duration(1,0,0,0)) as the Source line, then convert to table, and add date columns.  Or, if you have a Date table already, you can just filter it with datefilter.

Regards,

Pat

[sohananahid]

Hi @mahoneypat : Thanks for your response. I don't have a date time table. So, I tried to create a new source as a blank query as you mentioned, got this error:

I am trying to have a filter mechanism  for 'Starting Date' [drop-down list with Month and Year] to  filter data on my event Table visual. After this query works, where do I invoke it to?

Many thanks in advance.