[go: up one dir, main page]
Scribd
Upload
45 views9 pages

Power Query

The document outlines various time-based features in Power Query, including Year-to-Date (YTD), Year-to-Go (YTG), Quarter-to-Date (QTD), Month-to-Date (MTD), Rolling Period (Last 7 Days), Year-over-Year (YoY), and Fiscal Year Calculations. Each feature includes a description, when to use it, and the corresponding formula for implementation. These features are designed to assist in filtering and analyzing data based on specific time frames for better reporting and performance tracking.

Uploaded by

deepaksain013
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
45 views9 pages

Power Query

The document outlines various time-based features in Power Query, including Year-to-Date (YTD), Year-to-Go (YTG), Quarter-to-Date (QTD), Month-to-Date (MTD), Rolling Period (Last 7 Days), Year-over-Year (YoY), and Fiscal Year Calculations. Each feature includes a description, when to use it, and the corresponding formula for implementation. These features are designed to assist in filtering and analyzing data based on specific time frames for better reporting and performance tracking.

Uploaded by

deepaksain013
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 9

POWERFUL TIME-BASED FEATURES

IN

POWER QUERY

@PREMMANDAL CLEAN AND SHORT PDF FOR YOU


BUSINESS & DATA ANALYST EASY TO LEARN
1 Year-to-Date (YTD)

🔹 What is it?
Filters data from the start of
the year to the current date.
🔹 When to use it?
Use YTD for reports that
need to show cumulative
data for the current year.

🔹 Formula:
CurrentDate = DateTime.LocalNow(),
YTDData = Table.SelectRows(Source, each
[DateColumn] <= CurrentDate)
2 Year-to-Go (YTG)

🔹 What is it?
Filters data from today to
the end of the current year.
🔹 When to use it?
Helpful when projecting
future trends or goals for the
rest of the year.

🔹 Formula:
CurrentDate = DateTime.LocalNow(),
YTGData = Table.SelectRows(Source, each
[DateColumn] > CurrentDate)
3 Quarter-to-Date (QTD)

🔹 What is it?
Filters data from the start of
the current quarter to today.
🔹 When to use it?
Ideal for quarterly financial or
performance reports.
🔹 Formula:
CurrentQuarter =
Date.QuarterOfYear(CurrentDate),
QTDData = Table.SelectRows(Source, each
Date.QuarterOfYear([DateColumn]) =
CurrentQuarter and [DateColumn] <=
CurrentDate)
4 Month-to-Date (MTD)

🔹 What is it?
Filters data from the start of
the month to the current date.
🔹 When to use it?
Great for tracking monthly
performance metrics.
🔹 Formula:
CurrentMonth = Date.Month(CurrentDate),
MTDData = Table.SelectRows(Source, each
Date.Month([DateColumn]) =
CurrentMonth and [DateColumn] <=
CurrentDate)
5 Rolling Period (Last 7 Days)

🔹 What is it?
Filters data for the past 7
days.

🔹 When to use it?


Perfect for tracking short-term
trends, such as daily sales or
traffic.

🔹 Formula:
StartDate = Date.AddDays(CurrentDate, -7),
Last7DaysData = Table.SelectRows(Source,
each [DateColumn] >= StartDate and
[DateColumn] <= CurrentDate)
6 Year-over-Year (YoY)

🔹 What is it?
Compares the current date range with
the same period last year.
🔹 When to use it?
Helpful for understanding how
performance has changed from the
previous year.

🔹 Formula:
LastYearDate = Date.AddYears(CurrentDate, -1),
YoYData = Table.SelectRows(Source, each
[DateColumn] >= Date.From(LastYearDate) and
[DateColumn] <= Date.From(CurrentDate))
7 Fiscal Year Calculations

🔹 What is it?
Filters data for a custom fiscal year,
not the calendar year.
🔹 When to use it?
Essential for organizations that operate
on a fiscal year (e.g., April–March).
🔹 Formula:
FiscalYearStartMonth = 4,
FiscalYear = if Date.Month(CurrentDate) >=
FiscalYearStartMonth then Date.Year(CurrentDate)
else Date.Year(CurrentDate) - 1,
FiscalYTDData = Table.SelectRows(Source, each
[DateColumn] >= #date(FiscalYear,
FiscalYearStartMonth, 1) and [DateColumn] <=
CurrentDate)
@PREMMANDAL
BUSINESS & DATA ANALYST

Was this helpful


WOULD YOU MIND SHOWING YOUR SUPPORT
BY GIVING IT A LIKE?
INSPIRE ME TO CREATE MORE!

You might also like