Date.IsInPreviousNDays M code question

[Barry_Kelly]

Date.IsInPreviousNDays([Text Range], 2)

I'd like to use this function to dynamically query a fact table based on a slicer selection. Being unfamiliar with M, can another query, function or customized function be substitued for where the whole number goes, in this case 2.

Thanks

[MarcelBeug]

Yes (in answer to the original question), provided the outcome is a value in the range of a 32-bit integer value.

If the value is a negative integer, the result will always be false.

Otherwise the first argument of the function should be a date/datetime or datetimezone field.

[Text Range] doesn't sound like such.

Edit: note that he comparison is always against the date of the last data refresh.

[Barry_Kelly]

[Text Range] is a column of dates in this example.

Actually, I read some of your previous posts that led me to how I can Query the results from an Excel table that has an Excel slicer.
Using the Aggregate function in Excel  it's possible to create a single row table in Power query based on the number of days a user selects.

I'm trying to determine if its possible to plug that result into the M function.

[dkay84_PowerBI]

From M you can pass a parameter but not a slicer selection to replace the hard coded number of days (in addition to a query or formula that returns a number).

[Barry_Kelly]

Can you give me an example of what the code/function would look like that would be accepted by the Date.ISN.. function?

[dkay84_PowerBI]

Any query or function/formula that returns a numeric value could be used in place of a hard coded value.  You could also refer it to the value returned by a previous step in the existing query or even a value from a specifc cell.

Based on your description of wanting to use a slicer, you are implying that you want the user to be able to select a value from said slicer and pass that into the filter argument for number of previous days to show.  If that is the case, if you provide more detail about what it is you want to return after the slicer selection (an aggregation/calculation or a filtered table), we can assist you with the approach.

[Barry_Kelly]

This is a representaion of the Excel Table. I'll followup with two more posts  showing the Power Query side.

[Barry_Kelly]

This shows the results from the slicer in Power Query.

[Barry_Kelly]

The question is how to use the results from the parameter table in the Date.IsInPreviousNDays function.

[MarcelBeug · Accepted solution]

You can either:

Add 1 step to your first query: select the value, so the output of the query will be 1 value and you can replace the 2 in your function by <NameOfYourFirstQuery>.

Or

Replace the value 2 in your function by: Table.FirstValue(<NameOfYourFirstQuery>).

I would prefer the first option.

[Barry_Kelly]

Thank you very much!! I could not get your first solution to work and was working on an approach to include a "Let" statement.

The Table.FirstValue works in the let statement. No doubt this approach can be improved upon but I now have a simple solution that allows the user to dynamically change the Power Query results.

[MarcelBeug]

Glad your issue is solved.

Sorry I forgot to mention with my first option: you should right-click the value in the table (in the Query Editor) and choose "Drill down" from the menu.

[dkay84_PowerBI]

I'm not sure I understand your objective.  Are you trying to replace the hard coded "2" with a dynamic value?