Forum Discussion

JP-Ronse's avatar
JP-Ronse
Icon for Helper II rankHelper II
8 years ago
Solved

year to date

Hi,

 

I have a huge table with about 90,000 rows, one column contains the date starting somewhere in 2015. Each date is repeated several times which explains the number of rows.

 

If I put a filter on that column I get easily all dates of last year but what I need are the dates from today at last year to today. There is an option YTD but it doesn't work the way I did.

 

Basically: =if(date >= edate(today(),-12),"include", "ignore")

 

Kind regards,

 

JP-Ronse

Need Help

11 Replies

    • JP-Ronse's avatar
      JP-Ronse
      Icon for Helper II rankHelper II
      8 years ago

      Hi,

       

      Thanks for the swift reply, I have

       

      let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DAY", type datetime}, {"THRESHOLD", Int64.Type}, {"MIN_OCCURENCES", Int64.Type}, {"METRIC", type text}, {"RESOURCE_NAME", type text}, {"CONT_BREACHING_COUNT", Int64.Type}, {"SEPARATED_BREACHING_COUNT", Int64.Type}, {"islast<I1>months", type any}, {"12", type any}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each DATESINPERIOD([DAY],DATE(2018,01,03),-12,month))
in
    #"Filtered Rows"

      But it says: The name 'DATESINPERIOD' wasn't recognized.

       

      Secondly, the todays date should be adapted automatically.

       

      (I am a new, sorry for all the stupid questions)

       

      I am using Excel 2013 with PQ add-in.

       

      Kind regards,

       

      JP

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi JP-Ronse,

     

    To use the solution suggested by WolfBiber, you need to create a calendar table first, and create a one to many relationship between calendar table and your source table.

    DateTime=CALENDAR(DATE(2015,1,1), DATE(2018,12,31))

     

    By the way, all above functions are DAX functions which should be used in report view mode rather than Query Editor mode.

     

    Regards,

    Yuliana Gu