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

[Anonymous]

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. 🙂 

[mahoneypat]

Look back at previous post, the first function was List.Dates to start making a date table (not Date.ToText).  You then need to convert that list to a table (ribbon button) and add the columns you want (Month, Year, etc.).

For the slicer, are you trying to let the use pick starting date before the API calls are made?  If so, then you are talking about using a parameter, saving the file as a pbit (template).  The user would be prompted to enter a start date and then the data would be fetched.

If you are looking for a slicer after the data are returned, then you can use one of the columns from your new Date table.

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

Regards,

Pat

[Anonymous]

Hi @mahoneypat : Thanks for your response. I tried the query from original post in the first place, but got this:

After I put a parenthesis [I tried to put it in 3 different places to make it work! :)] but I get this error:

[to answer to your q: I am using the slicer after the api call.] Thanks in advance.

[mahoneypat]

My bad.  Sorry.  The datefilter is in text format, so you need to either use the non-text portion of that query in place of "datefilter"

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

Or wrap datefilter in Date.FromText( ).

For example on the first one, here is some M code as an example.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.

let
    Source = Date.AddMonths(Date.From(DateTime.LocalNow()), -1),
    Custom1 = List.Dates(Source, Duration.TotalDays(Date.From(DateTime.LocalNow()) - Source), #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
    #"Inserted Month Name" = Table.AddColumn(#"Changed Type", "Month Name", each Date.MonthName([Date]), type text),
    #"Inserted Year" = Table.AddColumn(#"Inserted Month Name", "Year", each Date.Year([Date]), Int64.Type)
in
    #"Inserted Year"

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

Regards,

Pat