Time Intelligence in EXCEL using LAMBDA

This example of LAMBDA helps while working with sales data and need to summarize it quickly based on specific years ! The breakdown of the formula which supports video posted here !
𝐅𝐮𝐧𝐜𝐭𝐢𝐨𝐧 𝐟𝐨𝐫 𝐂𝐮𝐫𝐫𝐞𝐧𝐭𝐘𝐞𝐚𝐫
=𝘓𝘈𝘔𝘉𝘋𝘈(𝘺𝘦𝘢𝘳,𝘥𝘢𝘵𝘦𝘳𝘢𝘯𝘨𝘦,𝘴𝘢𝘭𝘦𝘴𝘳𝘢𝘯𝘨𝘦,
𝘚𝘜𝘔(𝘍𝘐𝘓𝘛𝘌𝘙(𝘴𝘢𝘭𝘦𝘴𝘳𝘢𝘯𝘨𝘦,𝘠𝘌𝘈𝘙(𝘥𝘢𝘵𝘦𝘳𝘢𝘯𝘨𝘦)=𝘺𝘦𝘢𝘳)))

Here's what each part of the function does:
𝒚𝒆𝒂𝒓: The year you want to filter for.
𝒅𝒂𝒕𝒆𝒓𝒂𝒏𝒈𝒆: A column or range containing dates.
salesrange: A column or range containing sales values.

𝑭𝑰𝑳𝑻𝑬𝑹(𝒔𝒂𝒍𝒆𝒔𝒓𝒂𝒏𝒈𝒆,𝒀𝑬𝑨𝑹(𝒅𝒂𝒕𝒆𝒓𝒂𝒏𝒈𝒆)=𝒚𝒆𝒂𝒓): Filters the 𝒔𝒂𝒍𝒆𝒔𝒓𝒂𝒏𝒈𝒆 to only include values where the 𝒚𝒆𝒂𝒓 in the 𝒅𝒂𝒕𝒆𝒓𝒂𝒏𝒈𝒆 matches the given year.

𝑺𝑼𝑴: Adds up all the filtered sales values.

When you call this LAMBDA function, it dynamically calculates the total sales for a specified year without needing to create helper columns or manual filtering.

𝑭𝒖𝒏𝒄𝒕𝒊𝒐𝒏 𝒇𝒐𝒓 𝑺𝒂𝒎𝒆𝑷𝒆𝒓𝒊𝒐𝒅𝑳𝒂𝒔𝒕𝒀𝒆𝒂𝒓 (😜 lookalike Power BI Function?!)
=𝘓𝘈𝘔𝘉𝘋𝘈(𝘺𝘦𝘢𝘳,𝘥𝘢𝘵𝘦𝘳𝘢𝘯𝘨𝘦,𝘴𝘢𝘭𝘦𝘴𝘳𝘢𝘯𝘨𝘦,
𝘐𝘍𝘌𝘙𝘙𝘖𝘙(
𝘚𝘜𝘔(𝘍𝘐𝘓𝘛𝘌𝘙(𝘴𝘢𝘭𝘦𝘴𝘳𝘢𝘯𝘨𝘦,𝘠𝘌𝘈𝘙(𝘥𝘢𝘵𝘦𝘳𝘢𝘯𝘨𝘦)=𝘺𝘦𝘢𝘳-1)),
"𝘕𝘰 𝘗𝘳𝘦𝘷𝘪𝘰𝘶𝘴 𝘠𝘦𝘢𝘳"))

What’s happening here?
𝒚𝒆𝒂𝒓-1 (read as year minus 1): This is where we look at the previous year. If you pass 2024 as the year, it calculates for 2023. The -1 simply shifts the filter to target one year earlier.

𝑰𝑭𝑬𝑹𝑹𝑶𝑹: Sometimes, there might not be any data for the previous year (e.g., your dataset starts in 2023, and you look for 2022). Without data, Excel could throw an error. The IFERROR function catches this and shows a friendly message like "𝑵𝒐 𝑷𝒓𝒆𝒗𝒊𝒐𝒖𝒔 𝒀𝒆𝒂𝒓"

Watch this video till the end for a simple, step-by-step guide to understanding and using this function effectively #creativedesigns #ExcelTips #DataAnalysis #ExcelMagic #SpreadsheetSkills #ExcelFunctions
#ProductivityTips #LearnExcel #ExcelAutomation #Office365 #DataDriven #BusinessIntelligence #EfficiencyMatters #ExcelForBusiness #ExcelTutorial

Видео Time Intelligence in EXCEL using LAMBDA канала Vijay Perepa