Hekp with M Query

[smeg]

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

[Anonymous]

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.

[smeg]

Hi 

@Anonymous 

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

[wdx223_Daniel]

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

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