Issues Understanding Time Intelligence Functions in Power BI

nikhilrai Create a calendar table that includes a column for the fiscal year and fiscal month. This table should have a continuous range of dates and additional columns to represent fiscal periods.

When using functions like TOTALYTD, you can specify the fiscal year start month. For example, to calculate the Year-to-Date (YTD) total starting from April, you can use the TOTALYTD function with the year_end_date parameter set to March 31st.

DAX
Calendar =
ADDCOLUMNS (
CALENDAR (DATE(2020, 1, 1), DATE(2023, 12, 31)),
"Year", YEAR([Date]),
"Month", MONTH([Date]),
"Day", DAY([Date]),
"Fiscal Year", IF(MONTH([Date]) >= 4, YEAR([Date]), YEAR([Date]) - 1),
"Fiscal Month", IF(MONTH([Date]) >= 4, MONTH([Date]) - 3, MONTH([Date]) + 9)
)

YTD_Sales =
TOTALYTD (
[Total Sales],
'Calendar'[Date],
"03/31"
)

For year-over-year comparisons, you can use the SAMEPERIODLASTYEAR function in conjunction with your adjusted calendar table. Ensure that your measures reference the fiscal year columns appropriately.