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 certain values using YTD displaying figures for fiscal year 2024-25.

I wish to create a column chart displaying sales amount not only for 2024-25 but for all the years upto the selected

month of those fiscal years.  For example, June 24 selected in slicer, I want sales for all fin years upto june of respective

years.

I used the measure Calculate[sales], ALL(Cal[FY)) which gives only sales of FY 2024-25.

How to construct a measure so that my column chart will show sales for all FY but upto the selected month

of respective years.

Thanks.

Need Help

6 Replies

    • 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