Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Community Champion
Community Champion

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
Community Champion
Community Champion

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors