Forum Discussion

Helper I

2 years ago

Solved

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

smeg
2 years ago
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.

4 Replies

Anonymous
Not applicable
2 years ago
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
  Helper I
  2 years ago
  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
    Community Champion
    2 years ago
    = Number.From(Date.ToText(Date.AddMonths( Date.StartOfMonth( DateTime.LocalNow() ) , -18),"yyyyMMdd"))