Forum Discussion
Aggregate data by month, count rows that meet condition and filter by not grouped by columns
Community SupportHI 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
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