Forum Discussion

Thulasiram's avatar
Thulasiram
Helper II
2 years ago

Remove partial filter

Dear all, I have prepared a report page with card and matrix visuals and the following hierarchial slicer for Fiscal year and months.   I have chosen a month - June 24 - and presented certai...
Need Help
Thulasiram's avatar
Thulasiram
Helper II
2 years ago

Thanks for reply.  I went thro the article you referred.  Since I am not a techi and a newbie, I couldn't comprehend it.  can someone pls help.

  • hackcrr's avatar
    hackcrr
    Memorable Member
    2 years ago

    Hi, Thulasiram 

    Ensure you have a comprehensive Date table that includes columns for fiscal year, month, and day. This table should be marked as a Date table in Power BI.

    Create a Measure to Calculate Sales Up to the Selected Month:

    SalesUpToSelectedMonth = 
VAR SelectedMonth = MAX('Date'[Month])
VAR SelectedYear = MAX('Date'[Fiscal Year])
RETURN
CALCULATE(
    SUM('Sales'[SalesAmount]),
    FILTER(
        ALL('Date'),
        'Date'[Fiscal Year] <= SelectedYear &&
        (
            'Date'[Fiscal Year] < SelectedYear ||
            'Date'[MonthNumber] <= SelectedMonth
        )
    )
)

    Add a column chart to your report. Use the fiscal year as the x-axis. Use the SalesUpToSelectedMonth measure as the values.

    Make sure your Date table includes a month number column (MonthNumber), which represents the month as a number from 1 to 12.

    Ensure that your slicer interaction is correctly set so that selecting a month filters your visuals appropriately.
    You might need to adjust the relationships and interactions to ensure the slicer filters your data as expected.

     

    If this post helps, then please consider Accept it as the solution and kudos to this post to help the other members find it more quickly

    • Thulasiram's avatar
      Thulasiram
      Helper II
      2 years ago

      Thanks.  Tried but getting the following error.

      • hackcrr's avatar
        hackcrr
        Memorable Member
        2 years ago

        Hi, Thulasiram 

        The error indicates that there is a type mismatch between the SelectedMonth and Date[MonthNumber] values. To fix this, we should ensure both values are of the same type. If one is a text and the other is an integer, we can use the VALUE function to convert the text to a number or the FORMAT function to convert the number to text.

        Here's the updated DAX measure:

        SalesUpToSelectedMonth = 
VAR SelectedMonth = VALUE(MAX('Date'[Month])) -- Ensure month is treated as a number
VAR SelectedYear = MAX('Date'[Fiscal Year])
RETURN
CALCULATE(
    SUM('Sales'[SalesAmount]),
    FILTER(
        ALL('Date'),
        'Date'[Fiscal Year] <= SelectedYear &&
        (
            'Date'[Fiscal Year] < SelectedYear ||
            VALUE('Date'[MonthNumber]) <= SelectedMonth
        )
    )
)

         

         

        If this post helps, then please consider Accept it as the solution and kudos to this post to help the other members find it more quickly