Forum Discussion

alderete-tomas's avatar
alderete-tomas
Frequent Visitor
3 years ago

Combine tables within different date between them

The model has several queries where there are three that I need to match. First, a table of people containing ID+(yearmonth) as a unique ID but it is not connected to calendar as the table people has no date column; Second, a table containing salary by people (the same key that People's table) connected to People and Calendar; the third, Attendance connected by key to People and by date to Calendar. I need to get the sumproduct between salary and certain states of attendance by people, to get the ammount paid, and then, do the same but get the same attendance's states for the previous month.

To be more exact, these are the measures:
 

1. BI_HC = SUMX(SUMMARIZE(Personas,Personas[Key],"BIHC",[Remuneración (Sin Horas Extras) Mes Ant]*[Operativos]),[BIHC])
Where [Operativos] get the sum of certain attendance states of actual month, [Remuneración (Sin Horas Extras) Mes Ant] retrieving salaries but for the previous month.

2. BI_HC Mes Ant =SUMX(SUMMARIZE(Personas,Personas[Key],"BIHCA",[Remuneración (Sin Horas Extras) Mes Ant]*[Operativos Mes Ant]),[BIHCA])
This one does the same that the other one but both measures used ([Remuneración (Sin Horas Extras) Mes Ant] and [Operativos Mes Ant]) are retrieved from previous month. 
 
 The problem is that N°1 doesn't return a number, while the second does. How can I match different measures with different month, by People? Or it is not possible?
 
 



1 Reply

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    3 years ago

    Its not a good idea to add columns using SUMMARIZE, see this article from SQLBI.

    Instead, use ADDCOLUMNS, e.g.

    BI_HC =
SUMX (
    ADDCOLUMNS (
        SUMMARIZE ( Personas, Personas[Key] ),
        "BIHC", [Remuneración (Sin Horas Extras) Mes Ant] * [Operativos]
    ),
    [BIHC]
)