Aggregate data by month, count rows that meet condition and filter by not grouped by columns

[PadilhaBI]

I have a table with the following columns:

• Date by day
• Value sold
• Goal value
• Company

I want to aggregate the value sold and goal value by month, then check wether each month surpassed it's goal, and count how many did it.

I managed to get this done by creating a new table and grouping the date by month and counting how many months met the criteria. The problem is I still want to use the company column to filter, so I can see how many months met the criteria for each selected company or combination of companies.

Any ideas on how to do it are welcome!

[v-luwang-msft]

HI @PadilhaBI ,

Try to create the below column:

Month = FORMAT('Table'[Date by day],"YYYY/MM")

then get the sum value according month:

SUMACCORDMONTH = CALCULATE(SUM('Table'[Value sold]),FILTER(ALL('Table'),'Table'[Month]=EARLIER('Table'[Month])&&'Table'[Company]=EARLIER('Table'[Company])))

And then use the below to get the account :

COUNTSATISFIED ACCORDING MON = CALCULATE(DISTINCTCOUNT('Table'[Company]),FILTER(ALL('Table'),'Table'[SUMACCORDMONTH]>='Table'[Goal value]&&'Table'[Month]=EARLIER('Table'[Month])))

Output result:

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[PadilhaBI]

Hey @v-luwang-msft, thanks for answering!

Your solution didn't actually work for my intended purpose. The resulting column: 

COUNTSATISFIED ACCORDING MON

shows me the count for how many companies met their goal each month. I wanted to check how many months met the aggregated goal, and be able to filter out companies to see the results.

The solution you proposed is static, what I mean by that is that it's not affected by filters.

Anyway I learned some new things from your post, thanks!

[v-luwang-msft]

Hi @PadilhaBI ,

Could you pls share a sample data,and expected output?

Remember to remove confidential data.

Best Regards

Lucien