Remove partial filter

[Thulasiram]

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.

[TomMartens]

Hey @Thulasiram ,

I recommend reading this article: Time patterns – DAX Patterns

This article contains almost everything one need to know about time/date related DAX calculations.

Regards,

Tom

[Thulasiram]

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]

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]

Thanks.  Tried but getting the following error.

[hackcrr]

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

[Thulasiram]

Many thanks for reply.

Before proceeding further i would like to explain further about my tabble.

Month is in MMM-YY format - text. (example - "Mar-24")

Fiscal year is YYYY-YY - text ("2024-25")
Month no - numbers - number - (3)

Can I still use the above code or it needs any amendment.  Many thanks.