Fiscal YTD calculation not respecting Month slicer in Power BI

[SavioFerraz]

Hello everyone,

I’m currently working on a Power BI report and I’m struggling a bit with a Year-to-Date calculation using a fiscal calendar. In my case, the fiscal year runs from July to June, and I have a proper Date table with Fiscal Year and Fiscal Month columns, along with a fact table that stores sales values. On the report page, I’m using two slicers: one for Fiscal Year and another one for Fiscal Month.

The issue I’m facing is that when I select a specific month, for example November, I expect my YTD calculation to accumulate values from July up to November of the selected fiscal year. However, the measure doesn’t always behave as expected. In some cases, it seems to ignore the selected month, and in other cases it accumulates values beyond the selected month, which makes the results confusing for end users.

I’m not using the built-in time intelligence functions because of the fiscal calendar, so I created a custom YTD measure using CALCULATE and FILTER with fiscal month logic. Even so, I’m still not confident that my approach is correct or aligned with best practices.

Has anyone dealt with a similar scenario? What is the recommended DAX pattern to correctly calculate Fiscal YTD while respecting both Fiscal Year and Fiscal Month slicers?

Thanks in advance for any guidance or suggestions.

[Kedar_Pande]

@SavioFerraz 

Fiscal YTD = 
CALCULATE(
SUM(Sales[Value]),
FILTER(
ALL('Date'),
'Date'[FiscalYear] = SELECTEDVALUE('Date'[FiscalYear]) &&
'Date'[FiscalMonth] <= SELECTEDVALUE('Date'[FiscalMonth]) &&
'Date'[FiscalMonth] >= 7
)
)

If this answer helped, please click 👍 or Accept as Solution.
-Kedar
LinkedIn: https://www.linkedin.com/in/kedar-pande

[Jihwan_Kim · Accepted solution]

Hi, I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

I tried to use new calendar option to simplify the DAX writing, and I tried to use disconnected fiscal month name table to create month name slicer.

Implement time-based calculations in Power BI - Power BI | Microsoft Learn

Please check the below picture and the attached pbix file.

Sales: = 
SUM(sales[sales])

Sales FiscalYTD: = 
VAR _fiscalmonthslicer =
    MAX ( 'Month name slicer'[Fiscal Month number] )
RETURN
    IF (
        [Sales:] && MAX ( 'Calendar'[Fiscal Month number] ) <= _fiscalmonthslicer,
        CALCULATE ( [Sales:], DATESYTD ( 'fiscalcalendar' ) )
    )