DAX IF STATEMENT Not giving the desired result

[zaeyrox]

Hi all,

I am trying to do what I think it's something relatively simple. In a column chart I want to display the columns for the previous fiscal year if the current fiscal period is 1, otherwise just show the current fiscal year columns.

More straightforward: If we live in the fiscal period 1 of the current fiscal year, show then the columns for the previous fiscal year, otherwise just show the current fiscal year columns. 

In my Dim-FiscalCalendar, I have extra columns flagging if the current date belongs to the current (or previous) fiscal year or fiscal period. When that happens the flag is 1 otherwise 0

Here is a sample of the current fiscal period in my calendar (you can see the flags)

I have another table with the data. and it's also a very simple table

The relationship between tables is one to many using the columns Date:

My ideal scenario would be the following
When current fiscal Period is 1 then show the full previous fiscal year like this:

But if the current fiscal period is not 1, then just show the current fiscal year:

I created a DAX like this:

Recordables YTD = 
// Get the Attribute
VAR _Attributte = "Recordable"

// Get the current fiscal period
VAR _CurrentFiscalPeriod = 
CALCULATE(
    MAX('Dim-FiscalCalendar'[Fiscal Period]), 
    'Dim-FiscalCalendar'[FLAG_CurrentFiscalPeriod] = 1
)

RETURN
IF(
    // If current fiscal period is 1
    _CurrentFiscalPeriod = 1,
    // Then return previous fiscal year data
    CALCULATE(
        SUM('12 Safety Measurements'[Value]), 
        '12 Safety Measurements'[Attribute] = _Attributte, 
        'Dim-FiscalCalendar'[FLAG_PreviousFiscalYear] = 1
        ),
    // Otherwise return current fiscal year data
    CALCULATE(
        SUM('12 Safety Measurements'[Value]),
        '12 Safety Measurements'[Attribute] = _Attributte,
        'Dim-FiscalCalendar'[FLAG_CurrentFiscalYear] = 1
    )
)

But I got this:

What is super super weird is that if I invert the conditions in the if statement 
Like this:

I get this result (also not the ideal but worth to mention)

You might be asking: why dont you use the filters pane and apply filters on this visual or this page?

Well, the answer to that question is that I have almost 100 metrics that I visualize like this and I would like to make it programmatically so I don't have to update my filters once a year for so many visuals. 

For context: I have been a PBI Developer only 1 year so this is my first time facing this type of issue. have mercy of me hehe

Thank you very much for your help and if you have an answer for this or another solution that might help me I would appreciate it. 

PS: English is not my first language. Apologies if I was not 100% Clear

[DataNinja777 · Accepted solution]

Hi @zaeyrox ,

The issue you're facing is a classic DAX problem involving filter context. Your _CurrentFiscalPeriod variable is being recalculated within the context of each individual bar on your chart's axis. This means it's not holding the single, overall "current fiscal period" value needed for your IF statement to work correctly across the entire visual. The solution is to force that variable to be calculated by ignoring the filters coming from the chart.

Here is the corrected DAX measure. The key change is the addition of the ALL() function, which resolves the filter context issue.

Recordables YTD = 
// Get the Attribute
VAR _Attributte = "Recordable"

// Get the current fiscal period, IGNORING the chart's axis filters
VAR _CurrentFiscalPeriod = 
    CALCULATE(
        MAX('Dim-FiscalCalendar'[Fiscal Period]), 
        'Dim-FiscalCalendar'[FLAG_CurrentFiscalPeriod] = 1,
        ALL('Dim-FiscalCalendar') // <-- This is the key change
    )

RETURN
IF(
    // If the overall current fiscal period is 1
    _CurrentFiscalPeriod = 1,
    // Then return previous fiscal year data
    CALCULATE(
        SUM('12 Safety Measurements'[Value]), 
        '12 Safety Measurements'[Attribute] = _Attributte, 
        'Dim-FiscalCalendar'[FLAG_PreviousFiscalYear] = 1
    ),
    // Otherwise return current fiscal year data
    CALCULATE(
        SUM('12 Safety Measurements'[Value]),
        '12 Safety Measurements'[Attribute] = _Attributte,
        'Dim-FiscalCalendar'[FLAG_CurrentFiscalYear] = 1
    )
)

In your original measure, when the chart was drawing the bar for "Fiscal Period 7, 2025," the DAX engine was trying to calculate _CurrentFiscalPeriod with a filter of Fiscal Period = 7 already applied. Since FLAG_CurrentFiscalPeriod = 1 is only true for period 1 of 2026, the variable would result in BLANK() for the 2025 bars, causing your IF statement to incorrectly choose the "false" path.

By adding ALL('Dim-FiscalCalendar') to the CALCULATE function, you tell DAX to temporarily remove all filters from the calendar table before calculating the variable. This ensures _CurrentFiscalPeriod correctly and consistently returns 1 for every data point on the visual, regardless of its position on the axis. Now, your IF statement will always evaluate to TRUE (based on your current data), and the measure will correctly execute the calculation for the previous fiscal year. Power BI will automatically hide the bars for other periods because the measure returns BLANK() for them, giving you the exact chart you wanted. This dynamic approach is far superior to manual filtering, as it will automatically adapt when you move into the next fiscal period.

Best regards,

[d_m_LNK]

Great response @DataNinja777 totally agree with the solution.  I would add that the REMOVEFILTERS() function is also an option in this case since in the variable you are just trying to find the max fiscal period regardless of the filtering.  

[zaeyrox]

Hi DataNinja777,

Thank you very much for such a great explanation. It worked perfectly fine.

I also learnt with this more about how the context of a calculation works 🙂 

Thank you so much.

[v-prasare]

Hi @zaeyrox,

We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

@DataNinja777 ,Thanks for your prompt response

Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support