Forum Discussion
Filter current month and previous months
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
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.
11 Replies
- AmiraBedh
Super User
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 ) - 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 ) 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.
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 - okTable 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] )
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
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.
Does anyone have an idea?
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.Dino, thank you very much.
It helped me a lot.
