Filter exact future month instead of "is in the next"

[Someone22]

Hello,

Could you please help to me? I stucked in this step :D. I have a matrix in Power Bi, which is show the changes of materials.

Currently i have 6 months, July, August, September, October, November, December. The months are dynamic, every cases(date of update) includes 6 months, the intervall change in every next month, so in July the first month will be 2025.August and the last months will be 2026.January. Month is a date format.

I would like to see the decreasing in every month in every separate matrix, but in the filter of Month i can not choose exact month, only just "is in the next" and if i choose more than 1 month, i can not sort the month column, only just the Total column. Basic filtering is not okay, because the months are changing.

Is there any possiblity to choose exact months and it is follow the changes?

For example:

First matrix: Material (local)  Next first month(July) --> In July it will be August, so it have to be dynamic

Second matrix: Material (local)  Next second month(August) --> In July it will be September, so it have to be dynamic

Third matrix: Material (local)  Next Third month(September) --> In July it will be Oktober, so it have to be dynamic

.

.

.

etc

Thank you in advance!

[DataNinja777 · Accepted solution]

Hi @Someone22 ,

To achieve a dynamic filter for exact future months instead of a rolling period, you can create a special calculated column in your date table using DAX. This provides a robust way to isolate each future month individually.

First, navigate to the Data view in Power BI, select your date table, and create a new column. You will use a DAX formula to assign a unique index number to each future month relative to the current date. Paste the following code into the formula bar, making sure to replace 'YourDateTableName'[Date] with the name of your own date column.

Future Month Index = 
VAR vToday = TODAY()
VAR vNextMonthStart = EOMONTH(vToday, 0) + 1
VAR vRowDate = 'YourDateTableName'[Date]

RETURN
IF(
    vRowDate >= vNextMonthStart,
    (YEAR(vRowDate) - YEAR(vNextMonthStart)) * 12 + MONTH(vRowDate) - MONTH(vNextMonthStart) + 1,
    BLANK()
)

This formula sets an index where the next month is assigned the number 1, the month after that is 2, and so on, while leaving past and current months blank. Once this column is created, you can use it to filter your individual matrix visuals. For each matrix, drag the new Future Month Index column into the "Filters on this visual" pane. To show the very next month, set the filter value to 1. For the second matrix, which should show the second future month, set its filter value to 2. You would continue this pattern for all six of your matrices. This method is fully dynamic; as soon as the month changes, your reports will update automatically to show the correct corresponding future month data without any manual changes.

Best regards,

[Someone22]

Hello @DataNinja777 

Thank you for your quick reply, it is working! 🙂