Countrows if for each month

[Anonymous]

Hello,

I have three tables, two with different type of code, each table has a code, an amount column and a date table. 

I have a SUMUP table wich is construct like that :

- Month

- Countrows of the first table

- Sum of the amount from the first table

- Countrows of the second table

- Sum of the amount from the second table

I want to add 2 columns to my SUMUP table:

- A countrows if 'table 3'[fruit] = "Apple" distribued for each month 

- A countrows if 'table 3'[fruit] = "banana" distribued for each month 

I created a pbix file in order to give you some view over my datas.

Best regard,

ScrappyS

[v-diye-msft]

Hi @Anonymous 

If you've fixed the issue on your own please kindly share your solution. if the above posts help, please kindly mark it as a solution to help others find it more quickly. If not, please kindly elaborate more. thanks!

[ryan_mayu]

@Anonymous 

Is this what you want?

column = CALCULATE(COUNTROWS(Feuil1),FILTER(Feuil1,Feuil1[Fruit]=earlier(Feuil1[Fruit])&&Feuil1[Month]=EARLIER(Feuil1[Month])))

If not, please share the expected result that you want.

[Anonymous]

@ryan_mayu  

No, my table looks like that : 

Best regards

[ryan_mayu]

@Anonymous 

Could you please share the sample data?

[Anonymous]

@ryan_mayu  sure you can find it here

[ryan_mayu · Accepted solution]

@Anonymous 

I didn't see table 3 in your sample file,so I created one.

please see if this is what you want

Table = 
var t1=SUMMARIZE(ADDCOLUMNS('Table 1',"_month",month('Table 1'[Month])),[_month],"countrowT1",COUNTROWS('Table 1'),"SUM CA T1",SUM('Table 1'[CA]))
var t2=SUMMARIZE(ADDCOLUMNS('Table 2',"_month",month('Table 2'[Month])),[_month],"countrowT2",COUNTROWS('Table 2'),"SUM CA T2",SUM('Table 2'[CA]))
VAR t3=SUMMARIZE(ADDCOLUMNS('Table 3',"_month",month('Table 3'[Month])),[_month],"countapple",CALCULATE(COUNTROWS('Table 3'),FILTER('Table 3','Table 3'[fruit]="apple")),"countbanana",CALCULATE(COUNTROWS('Table 3'),FILTER('Table 3','Table 3'[fruit]="banana")))
VAR t4=NATURALINNERJOIN(t1,t2)
return 
NATURALINNERJOIN(NATURALINNERJOIN(t1,t2),t3)

Hope this is helpful.