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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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)

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

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.