Forum Discussion

pedrogpg's avatar
pedrogpg
New Member
4 years ago

Filtering between 2 fact and 2 dimension tables

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
Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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's avatar
      pedrogpg
      New Member
      4 years ago

      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]))