cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PhilC
Resolver I
Resolver I

Using value from another query in a custom column calculation

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

1 ACCEPTED SOLUTION
Jimmy801
Super User III
Super User III

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

View solution in original post

2 REPLIES 2
Jimmy801
Super User III
Super User III

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

View solution in original post

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors