Power BI Slicer with Slider not working with Previous Year Measure

[SHullen]

I have a table with only year (no date) so I found a measure that will give me previous year profit.  The problem is that when I attempt move the slider for net profit, previous year values disappear.  The calculation works until I change the slicer.

0_Profit_LY =
VAR Prev_Year = MAX( vw_Course_Profitability[FY_Dt] ) - 1
RETURN
CALCULATE(
SUMX( vw_Course_Profitability,vw_Course_Profitability[Net Profit]),
vw_Course_Profitability[FY_Dt] = Prev_Year
, REMOVEFILTERS(vw_Course_Profitability[Fiscal_Year] ) )

Thanks in advance!

Scott

[v-hjannapu · Accepted solution]

Hi @SHullen  ,
The issue is happening because the Net Profit slicer is affecting both the current year selection and the previous year calculation. Due to this, when the slicer filters out some values, the previous year rows may also get filtered completely and show blanks.

In the earlier approach, removing the filter completely was returning the full previous year total, which was not matching your requirement.

To keep the slicer working properly, the idea is to remove the Net Profit filter only while identifying the base year, but still allow the slicer to apply on the final previous year result.

I tested the below approach with different slicer selections and it was able to keep the previous year values aligned with the slicer filter instead of returning full totals or blanks unexpectedly.

0_Profit_LY :=
VAR BaseYear =
    CALCULATE(
        MAX(vw_Course_Profitability[FY_Dt]),
        REMOVEFILTERS(vw_Course_Profitability[Net Profit])
    )
VAR Prev_Year = BaseYear - 1
RETURN
    CALCULATE(
        SUM(vw_Course_Profitability[Net Profit]),
        vw_Course_Profitability[FY_Dt] = Prev_Year
    )

Hope this helps if you have any queries we are  happy to assist you further.
Regards,
Community Support Team.

[Ashish_Mathur]

Hi,

From the year column, you should first create a proper date and then build a calendar table.  Thereafter, you may simply create this measure

Measure = calculate(sum(vw_Course_Profitability,vw_Course_Profitability[Net Profit]),previousyear(calendar[dae]))

To receive further help, share some the download link of the PBI file and show the expected result.

[Shai_Karmani]

The Net Profit slicer is still applying inside your CALCULATE. REMOVEFILTERS only ignores Fiscal_Year, so the slider keeps filtering [Net Profit] when the engine evaluates the previous year. When the slider range does not match any prior year rows you get a blank.

Add the slicer column to your REMOVEFILTERS:

0_Profit_LY = 
VAR Prev_Year = MAX( vw_Course_Profitability[FY_Dt] ) - 1
RETURN
CALCULATE(
    SUM( vw_Course_Profitability[Net Profit] ),
    vw_Course_Profitability[FY_Dt] = Prev_Year,
    REMOVEFILTERS( vw_Course_Profitability[Fiscal_Year] ),
    REMOVEFILTERS( vw_Course_Profitability[Net Profit] )
)

One thing worth knowing: MAX(FY_Dt) reads the current filter context, so if the slider hides the latest year entirely, MAX will fall back to an earlier year. If you always want the most recent year as the base regardless of the slider, wrap MAX inside CALCULATE with ALL on [Net Profit] as well.

If it solved your issue, please mark it as the accepted solution and give it a kudos.

Thanks,
Shai Karmani

Let's connect in LinkedIn

[SHullen]

Thanks for your help Shai.  This kind of works however the previous year now shows the total for the previous year.  I want the previous year to filter with the slicer as well.  So in this case, it would filter out rows where the net profit is less than zero based on the slicer values.

[v-hjannapu · Accepted solution]

Hi @SHullen  ,
The issue is happening because the Net Profit slicer is affecting both the current year selection and the previous year calculation. Due to this, when the slicer filters out some values, the previous year rows may also get filtered completely and show blanks.

In the earlier approach, removing the filter completely was returning the full previous year total, which was not matching your requirement.

To keep the slicer working properly, the idea is to remove the Net Profit filter only while identifying the base year, but still allow the slicer to apply on the final previous year result.

I tested the below approach with different slicer selections and it was able to keep the previous year values aligned with the slicer filter instead of returning full totals or blanks unexpectedly.

0_Profit_LY :=
VAR BaseYear =
    CALCULATE(
        MAX(vw_Course_Profitability[FY_Dt]),
        REMOVEFILTERS(vw_Course_Profitability[Net Profit])
    )
VAR Prev_Year = BaseYear - 1
RETURN
    CALCULATE(
        SUM(vw_Course_Profitability[Net Profit]),
        vw_Course_Profitability[FY_Dt] = Prev_Year
    )

Hope this helps if you have any queries we are  happy to assist you further.
Regards,
Community Support Team.

[SHullen · Accepted solution]

Thanks for your assistance.  This is now working as expected.