Filtering between 2 fact and 2 dimension tables

[pedrogpg]

Hi everybody

I haver the following scheme where:

Relação de Clientes = Customer Base (dimension)

Cód_Item = Item Base (dimension)

dCalendário = Calendar (dimension)

fEmpenho = Open Orders (fact)

fNFe = Sales Made (fact)

So I created this table which works perfectly until I insert the Item Base dimension in it:

The measure that isn't working is:

Qtd. Faturado + Aberto = SUM(fEmpenho[Qtd. Aberta])+SUM(fNFe[Quantidade])

Which means: open orders amount + sales made amount

The strange thing is that these following measures are working perfectly and I don't know how or why:

Qtd mês anterior = CALCULATE(SUM(fNFe[Quantidade]),PREVIOUSMONTH(dCalendario[DataBase]))

Which means: open orders amount + sales made amount filtered by the previous month

Qtd Média 3 meses = CALCULATE(SUM(fNFe[Quantidade]),DATESINPERIOD(dCalendario[DataBase],LASTDATE(PREVIOUSMONTH(dCalendario[DataBase])),-3,MONTH))/3

Which means: open orders amount + sales made on last 3 months average

Qtd Média 6 meses =

CALCULATE(SUM(fNFe[Quantidade]),DATESINPERIOD(dCalendario[DataBase],LASTDATE(PREVIOUSMONTH(dCalendario[DataBase])),-6,MONTH))/6

Which means: open orders amount + sales made on last 6 months average

So I believe that the problem has to do with the fact that dimension tables Cód_Item (Item Base) and dCalendario (Calendar) are not related, but I couldn't find a reason why filtering by Customer Base works fine and when adding Items it doesn't.

Obs: the same Item can be sold to many different customers.

I would appreciate a lot if anybody could help me to solve this problem.

Thanks in advance

Pedro

[amitchandak]

@pedrogpg , Based on what I got , Try like

CALCULATE(AverageX(values(dCalendario[Month Year]), calculate(SUM(fNFe[Quantidade]))),DATESINPERIOD(dCalendario[DataBase], eomonth(Max(dCalendario[DataBase]),0),-3,MONTH))

CALCULATE(AverageX(values(dCalendario[Month Year]), calculate(SUM(fNFe[Quantidade]))),DATESINPERIOD(dCalendario[DataBase], eomonth(Max(dCalendario[DataBase]),0),-6,MONTH))

[pedrogpg]

Hi @amitchandak 

Thanks for your reply.

The average measures are working perfectly.

The problem is with the measure 

Qtd mês anterior = CALCULATE(SUM(fNFe[Quantidade]),PREVIOUSMONTH(dCalendario[DataBase]))