Filter current month and previous months

[Claudioffj]

I have a table as shown below. It is possible to create calculated column so that when selecting month 3, for example, the result is month 3 plus months 2 and 1?

IDProj   Month  Year        Stats

1             1             2024     OK

2             2             2024     OK

3             3             2024     WIP

4             3             2024     WIP

5             4             2024     WIP

6             1             2024     OK

7             2             2024     OK

8             3             2024     WIP

9             3             2024     OK

10          4             2024     WIP

[Anonymous · Accepted solution]

Hi @Claudioffj ,

Please try this way:
Add a new table for creating the slicer:

Use this DAX to create the measure:

Measure = 
VAR _maxmonth = CALCULATE(MAX([Month]), FILTER(ALLEXCEPT('Table', 'Table'[ID]), [Month] <= SELECTEDVALUE(Slicer[Month])))
RETURN
IF( MAX([Month]) = _maxmonth, 1, 0)

Make the settings as shown in the figure below:

The final output is as below:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Claudioffj]

Dino, thank you very much.

It helped me a lot.

[Claudioffj]

Does anyone have an idea?

[Claudioffj]

Can someone help me? I'm stuck on this problem and haven't been able to think of or come up with a solution for it yet.

[Claudioffj]

Sure @AmiraBedh, imagine a table with the data below.

ID	Month	Year	Stats
1	1	2024	OK
2	1	2024	OK
3	1	2024	WIP
4	2	2024	OK
5	2	2024	WIP
3	2	2024	WIP
3	3	2024	WIP
5	3	2024	WIP

I would like to present in a table the data for the month selected by the slicer and all the other data from previous months, but excluding duplicate IDs. So filtering month 3 in the data above, the table would need to look like this.

ID	Month	Year	Stats
1	1	2024	OK
2	1	2024	OK
4	2	2024	OK
3	3	2024	WIP
5	3	2024	WIP

Likewise, if I select month 2 in the slicer, it would look like this.

ID	Month	Year	Stats
1	1	2024	OK
2	1	2024	OK
4	2	2024	OK
5	2	2024	WIP
3	2	2024	WIP

[Claudioffj]

Unfortunately, the solutions they provided didn't work for me.
I was doing some more tests and forgot to mention. As it's a monthly report, it's possible for the IDs to repeat, so ID 10 could appear in month 5, month 4, month 3, and so on.
So when filtering for month 4, it should only show ID 10 from month 4 and all the others from previous months that don't repeat.
It would look something like this:

id10 - month 5 - wip
id9 - month 5 - wip
id10 - month 4 - ok
id9 - month 4 - wip
id10 - month 3 - ok
id6 - month 3 - ok
id10 - month 2 - ok
id5 - month 2 - ok
id10 - month 1 - ok

Table filtered for month 4:
id10 - month 4 - ok
id9 - month 4 - wip
id6 - month 3 - ok
id5 - month 2 - ok

[AmiraBedh]

Can you gather all the info and reshare it? the more you clarify your request, the more things will be easy for us ti help you.

Cumulative Stats = 
VAR SelectedMonth = MAX('Table'[Month])  -- Assumes a single month selection
RETURN
SUMX(
    FILTER(
        SUMMARIZE(
            'Table',
            'Table'[IDProj],
            "LatestMonth", MAXX(FILTER('Table', 'Table'[Month] <= SelectedMonth), 'Table'[Month]),
            "Stats", MAXX(FILTER('Table', 'Table'[Month] <= SelectedMonth), 'Table'[Stats])  -- Adjust this line as needed
        ),
        [LatestMonth] = SelectedMonth
    ),
    [Stats]
)

[AmiraBedh]

You can create a measure for visibility and use it as a filter in your visual :

IsVisibleBasedOnMonth = 
VAR CurrentMonth = MAX('Table'[Month]) -- Current row month
VAR CurrentYear = MAX('Table'[Year]) -- Current row year
VAR SelectedMonth = SELECTEDVALUE('Table'[Month])
RETURN
IF(
    AND(CurrentYear = MAX('Table'[Year]), CurrentMonth <= SelectedMonth),
    1, 
    0
)

[Claudioffj]

@AmiraBedh 

When I filter by month 3 for example, the data for January and February does not appear. In fact, I don't need to count, just show the data in a list table, listing the data according to the month filters applied.

selecting 3 in the filter..
ex. table
data month 3
data month 2
data month 1

Thanks

[Ashish_Mathur]

Hi,

I have solved a similar problem in the attached file.  On selecting a date, the table visual will show data for the 7 days ended the selected date.  You may apply the same logic for months as well.

Hope this helps.

[AmiraBedh]

Create a slicer for the month and then a measure : 

ProjectsUpToSelectedMonth = 
VAR SelectedMonth = MAX(MyTab[Month]) 
VAR SelectedYear = MAX(MyTab[Year])
RETURN
CALCULATE(
    COUNTROWS(MyTab),
    MyTab[Year] = SelectedYear,
    MyTab[Month] <= SelectedMonth
)