Year-to-Date Measure for all Fiscal Years?

[Dave1mo1]

Hi all,

I need to have YTD calculations that can take the fiscal year as a category on a waterfall visual, or as the x-axis on a column chart. For example - Today is May 27th, and I only want to see YTD totals through May 27th for 2025, 2024, 2023, etc. on my chart. However, when I use the TotalYTD function, I get the entirety of the previous years' totals through the end of the year for prior fiscal years.

I've previously used TotalYTD function and sameperiodlastyear to get separate YTD and PYTD measures, but now I essentially need those combined into one measure. I've tried various workarounds and have not been successful - can anyone help?!

Thanks!

[techies · Accepted solution]

Hi @Dave1mo1 please check this, since fiscal year ends on Sept 30, so Oct 1 is start

Sales FY YTD =
CALCULATE(
SUM('Sales'[Amount]),
DATESYTD(
'DateTable'[Date],
"9/30" 
)
)

also your date table has these columns

Fiscal Year =

VAR FiscalStartMonth = 10

RETURN

    IF(

        MONTH('date'[Date]) >= FiscalStartMonth,

        "FY" & YEAR('date'[Date]) + 1,

        "FY" & YEAR('date'[Date])

    )

Fiscal Month =

VAR FiscalStartMonth = 10

VAR CalendarMonth = MONTH('date'[Date])

RETURN

    IF(

        CalendarMonth >= FiscalStartMonth,

        CalendarMonth - FiscalStartMonth + 1,

        CalendarMonth + (12 - FiscalStartMonth + 1)

    )

[v-pgoloju]

Hi @Dave1mo1,

Just a gentle reminder  has your issue been resolved? If so, we’d be grateful if you could mark the solution that worked as Accepted Solution, or feel free to share your own if you found a different fix.

This not only closes the loop on your query but also helps others in the community solve similar issues faster.

Thank you for your time and feedback!

Best,

Prasanna Kumar

[v-pgoloju]

Hi @Dave1mo1,

Just following up to see if the solution provided was helpful in resolving your issue. Please feel free to let us know if you need any further assistance.

If the response addressed your query, kindly mark it as Accepted Solution and click Yes if you found it helpful  this will benefit others in the community as well.

Best regards,

Prasanna Kumar

[Poojara_D12 · Accepted solution]

hi @Dave1mo1 

FiscalYTD_UptoToday =

VAR SelectedFiscalYear = SELECTEDVALUE('Date'[FiscalYear])

// Fiscal Year Start Date: Assuming fiscal year starts Jan 1 for simplicity, else adjust here
VAR FiscalYearStartDate = DATE(SelectedFiscalYear, 1, 1)

// Fiscal Year End Date capped at May 27 (same year)
VAR FiscalYearCutoffDate = DATE(SelectedFiscalYear, 5, 27)

// Today (current system date)
VAR TodayDate = TODAY()

// Get the cutoff date to use for YTD (can't be beyond May 27 for that fiscal year, nor beyond today)
VAR CutoffDate = 
    IF(TodayDate < FiscalYearCutoffDate, TodayDate, FiscalYearCutoffDate)

// Calculate YTD sum from fiscal start to cutoff date
RETURN
CALCULATE(
    SUM('Fact'[Sales]),

    // Filter the date table between fiscal year start and cutoff date
    FILTER(
        ALL('Date'),
        'Date'[Date] >= FiscalYearStartDate &&
        'Date'[Date] <= CutoffDate
    )
)

• For each fiscal year on the axis, it sums sales only up to May 27 of that year (or today’s date if today is before May 27).

• Previous years no longer sum the full year but only Jan 1 to May 27.

• This works dynamically across multiple years on your visual axis or category.

• If your fiscal year doesn’t start Jan 1, replace FiscalYearStartDate logic with your fiscal year start date logic.

• If you want to account for time zones or fixed cutoff date different from system TODAY(), adjust accordingly.

[v-pgoloju]

Hi @Dave1mo1,

Thank you for reaching out to the Microsoft Fabric Forum Community.
And also thanks to @sjoerdvn  and @techies  for Prompt and helpful response.

Just following up to see if the solution provided was helpful in resolving your issue. Please feel free to let us know if you need any further assistance.

If the response addressed your query, kindly mark it as Accepted Solution and click Yes if you found it helpful  this will benefit others in the community as well.

Best regards,

Prasanna Kumar

[techies · Accepted solution]

Hi @Dave1mo1 please check this, since fiscal year ends on Sept 30, so Oct 1 is start

Sales FY YTD =
CALCULATE(
SUM('Sales'[Amount]),
DATESYTD(
'DateTable'[Date],
"9/30" 
)
)

also your date table has these columns

Fiscal Year =

VAR FiscalStartMonth = 10

RETURN

    IF(

        MONTH('date'[Date]) >= FiscalStartMonth,

        "FY" & YEAR('date'[Date]) + 1,

        "FY" & YEAR('date'[Date])

    )

Fiscal Month =

VAR FiscalStartMonth = 10

VAR CalendarMonth = MONTH('date'[Date])

RETURN

    IF(

        CalendarMonth >= FiscalStartMonth,

        CalendarMonth - FiscalStartMonth + 1,

        CalendarMonth + (12 - FiscalStartMonth + 1)

    )

[sjoerdvn]

Try something like below...

measure ytd-1 = CALCULATE(SUM(InternetSales_USD[SalesAmount_USD])
   , PARALLELPERIOD(DATESYTD(DateTime[DateKey]),-1,year))

[Dave1mo1]

Thanks so much! It looks like this works based on the calendar year, but I need to be able to adjust it for our fiscal year, which starts in October. Any tips on that front?

[sjoerdvn]

The DatesYTD has an optional parameter for that
So I Guess it would become 

DATESYTD(DateTime[DateKey],"9-30")