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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Barry_Kelly
New Member

Date.IsInPreviousNDays M code question

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



 

 

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

 

Specializing in Power Query Formula Language (M)

View solution in original post

12 REPLIES 12
MarcelBeug
Community Champion
Community Champion

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.

Specializing in Power Query Formula Language (M)

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

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

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

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.

Aggregate.PNG

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

Table1.PNG

This shows the results from the slicer in Power Query.

Calendar.PNG

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

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.

 

Specializing in Power Query Formula Language (M)

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.

Glad your issue is solved. Smiley Happy

 

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.

Specializing in Power Query Formula Language (M)
dkay84_PowerBI
Microsoft Employee
Microsoft Employee

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

Helpful resources

Announcements
Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors