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
smeg
Helper I
Helper I

Hekp with M Query

Hi,

     I am trying to retrieve  data into one of the query in power query editor  using M for  last 18 months,but below query retrieves 2 years of data.

Can you please let me know the formula to just retrieve last 18 months that is data from 06012022.

dbo_v_Ns_Metrics_1 = Table.SelectRows(dbo_v_Ns_Metrics, each [PE_Date_Int] > ((Date.Year(Date.AddDays(DateTime.LocalNow(), -1 )) - 2) * 10000) + 1231),

 

Thanks..

1 ACCEPTED SOLUTION

Below query  worked for me as per my requirement,to retrieve  data for  last 18 months,

As I also  need a number format not a date format.

smeg_0-1702488521424.png

 

View solution in original post

4 REPLIES 4
Daryl-Lynch-Bzy
Resident Rockstar
Resident Rockstar

Hi @smeg - if you enter the following function the result is 20211231:

= (((Date.Year(Date.AddDays(DateTime.LocalNow(), -1 )) - 2) * 10000) + 1231)

// 20221231

 This result is number, not a date.  

 

Could you use the following function instead.

= Date.AddMonths( Date.StartOfMonth( DateTime.LocalNow() ) , -18)

// 01/06/2022 00:00:00

Another thing to consider is the Data Type of the [PE_Date_Int] column.  Is it Date?  The example "06012022" suggest that it might be Text.

Hi 

Data Type of the [PE_Date_Int] column is integer(number)  not a date,How can i cast/convert below function accordingly because when i use below one the data refresh is taking forever,because of comparison between number and date.

= Date.AddMonths( Date.StartOfMonth( DateTime.LocalNow() ) , -18)

 

Thanks..

= Number.From(Date.ToText(Date.AddMonths( Date.StartOfMonth( DateTime.LocalNow() ) , -18),"yyyyMMdd"))

Below query  worked for me as per my requirement,to retrieve  data for  last 18 months,

As I also  need a number format not a date format.

smeg_0-1702488521424.png

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

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
Top Kudoed Authors