year to date

[JP-Ronse]

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

[v-yulgu-msft · Accepted solution]

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

[WolfBiber · Accepted solution]

😉 then use PowerBi Desktop, its for free

https://powerbi.microsoft.com/en-us/desktop/

and you can create date Tables in Excel 2016, don't know if in 2013 its also working

https://blog.crossjoin.co.uk/2015/06/26/automatically-generating-date-dimension-tables-in-excel-2016...

[v-yulgu-msft · Accepted solution]

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

[JP-Ronse]

Hi,

I am using PQ & Excel 2013, a calendar table is not available in my configuration.

Kind regards,

JP-Ronse

[parry2k · Accepted solution]

@JP-Ronse You can create calendar table in PQ as well, here is link for that , I'm sure there are more.

https://www.powerquery.training/portfolio/dynamic-calendar-table/

[JP-Ronse]

Hi All,

Thanks for all the feedback. I have to go back to school and that at my age (62)

There are a lot of interesting links in the replies I have to go through.

It is no really YTD but for now I keep it as:

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 Date.IsInYearToDate([DAY]) or Date.IsInPreviousNMonths([DAY], 12)),
    #"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Filtered Rows", {{"DAY", type text}}, "en-GB"),"DAY",Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv),{"DAY.1", "DAY.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"DAY.1", type date}, {"DAY.2", type time}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"DAY.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"DAY.1", "DAY"}}),
    #"Filtered Rows1" = Table.SelectRows(#"Renamed Columns", each ([METRIC] = "UTILIN")),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows1",{"islast<I1>months", "12"})
in
    #"Removed Columns1"

Thanks all,

JP-Ronse

[WolfBiber · Accepted solution]

😉 then use PowerBi Desktop, its for free

https://powerbi.microsoft.com/en-us/desktop/

and you can create date Tables in Excel 2016, don't know if in 2013 its also working

https://blog.crossjoin.co.uk/2015/06/26/automatically-generating-date-dimension-tables-in-excel-2016...

[WolfBiber]

Hey,

you can calculate rolling sum with 

= CALCULATE(SUM(InternetSales_USD[SalesAmount_USD]),DATESINPERIOD(DateTime[DateKey],DATE(2018,01,03),-12,month))  

Please refer to https://msdn.microsoft.com/en-us/library/ee634539.aspx

Greetings

[JP-Ronse]

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

[WolfBiber]

Hi

-->Secondly, the todays date should be adapted automatically.

This Task you can achieve with 

Today()

instead of creating a fixed Date

[WolfBiber]

Hey,

you put the code in the Query Editor. This programming language is called M.

I delivered you DAX Syntax.

[parry2k]

@JP-Ronse

Solution @WolfBiber is a DAX not to add in PQ.

Here is the link for DAX in Excel.

[JP-Ronse]

Hi,

 Ah OK, becomes to complex.  I am self a newbie and trying to help colleagues who never have heard about PQ & DAX but I think I've found something. Keep you posted.

Kind regards,

JP-Ronse