cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PhilC
Helper II
Helper II

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
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors