Using value from another query in a custom column calculation

[PhilC]

I have a query that has one column, one row, representing the REPORTING DATE.  It is the MAX(SNAPSHOT_DATE) from a separate query.

I want to use the REPORTING DATE in calculations (custom colum) in my Dates table, to created flags for:

 - Current Year

 - Previous Year

 - Current YTD

 - Previous YTD

Note, will also want to do Current 12M and Previous 12M flags as well

I am doing the following, but the load time has blown out and I am wondering if there is a more efficient approach (even back to getting the reporting date from SNAPSHOT_DATE field in the data table.

(Note: based on this solution:  Power-Query-add-column-getting-value-from-another-one-in-an 

#"Added Current Year" = Table.AddColumn(#"Filtered Rows", "Current Year", each Date.Year([Date]) = Date.Year(#"REPORTING DATE"[REPORTING DATE]{0})),
    #"Added Previous Year" = Table.AddColumn(#"Added Current Year", "Previous Year", each Date.Year([Date]) = Date.Year(#"REPORTING DATE"[REPORTING DATE]{0})-1),
    #"Added Current YTD" = Table.AddColumn(#"Added Previous Year", "Current YTD", each Date.Month([Date]) = Date.Month(#"REPORTING DATE"[REPORTING DATE]{0}) and [Current Year]),
    #"Added Previous YTD" = Table.AddColumn(#"Added Current YTD", "Previous YTD", each Date.Month([Date]) = Date.Month(#"REPORTING DATE"[REPORTING DATE]{0})-1 and [Previous Year]),

Thanks in advance

[Jimmy801 · Accepted solution]

Hello @PhilC 

yes, there is. The problem here is that your external table has to be reloaded on every row and on every step. Use Table.Buffer to buffer your table in your query, so it has only to be loaded once. Use this code instead

BufferedTable = Table.Buffer(#"REPORTING DATE"),
#"Added Current Year" = Table.AddColumn(#"Filtered Rows", "Current Year", each Date.Year([Date]) = Date.Year(BufferedTable [REPORTING DATE]{0})),
    #"Added Previous Year" = Table.AddColumn(#"Added Current Year", "Previous Year", each Date.Year([Date]) = Date.Year(BufferedTable[REPORTING DATE]{0})-1),
    #"Added Current YTD" = Table.AddColumn(#"Added Previous Year", "Current YTD", each Date.Month([Date]) = Date.Month(BufferedTable [REPORTING DATE]{0}) and [Current Year]),
    #"Added Previous YTD" = Table.AddColumn(#"Added Current YTD", "Previous YTD", each Date.Month([Date]) = Date.Month(BufferedTable[REPORTING DATE]{0})-1 and [Previous Year]),

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[PhilC]

Sensational.  Went from over 5 mins to a handful of seconds.  Much appreciated @Jimmy801