topic Find values that appear in 3 consecutive months in DAX Commands and Tips

Find values that appear in 3 consecutive months

Ngoc_Nguyen — Thu, 07 Sep 2023 07:09:13 GMT

Hello friends,

I would like to find values in column Name in table Fact that appear consistently in 3 consecutive months and list them in the table with their according values in column Percentage. I also have a YearMonth dim table that has a column that joins with column YearMonth in the fact table. Please have a look at the example below for better demonstration.

Any help is welcomed. Thanks so much for all.

Here is an example of my data.

YearMonth	Name	Percentage
202305	A	0.5
202304	A	0.3
202305	B	0.56
202302	B	0.33
202306	B	0.15
202307	B	0.67
202303	A	0.8

Here is what I want to achieve:

YearMonth	Name	Percentage
202305	A	0.5
202304	A	0.3
202303	A	0.8
202307	B	0.67
202306	B	0.15
202305	B	0.56

Re: Find values that appear in 3 consecutive months

lbendlin — Sat, 09 Sep 2023 22:29:23 GMT

There are many more scenarios that you need to explain and cover (what if there are more than three consecutive months?)

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

Re: Find values that appear in 3 consecutive months

Ngoc_Nguyen — Mon, 11 Sep 2023 02:27:47 GMT

hi,
tks so much for replying
perhaps i wasnt clear on what i want. The idea is that when i filter a month, i can get the table (including name, percentage) that have appeared in the last 3 months since the month I filtered. And about the scenario that you've mentioned, i think that if the name appeared more than 3 consecutive months, its also included in the last 3 months.

Hope this helps!
Example data:

YearMonth	Name	Percentage
202305	A	0.5
202304	A	0.3
202305	B	0.56
202302	B	0.33
202306	B	0.15
202307	B	0.67
202303	A	0.8

Result when filtering 07/2023:

YearMonth	Name	Percentage
202307	B	0.67
202306	B	0.15
202305	B	0.56

Result when filtering 05/2023:

YearMonth	Name	Percentage
202305	A	0.5
202304	A	0.3
202303	A	0.8

Re: Find values that appear in 3 consecutive months

lbendlin — Mon, 11 Sep 2023 19:13:53 GMT

Can I interest you in a chart based solution?

Re: Find values that appear in 3 consecutive months

Ngoc_Nguyen — Tue, 12 Sep 2023 01:21:09 GMT

Yes please, it would be great if it is a line chart