Forum Discussion
Advocate IIGeneric months in a graph
I need to generate a graph as follows:
I have a table with two date fields. The first is DataCriacao, which has the opening of so-called dates. The other is DataConclusao, with the dates on which the calls were closed.
But an open call in January, for example, is not necessarily closed in January.
I need to create a graphic that runs from January to December and that I can count the open calls and closed every month.
As it is now I can not, because the dates do not add up. I need to create a table or something generic dates.
renanpinheiro in really high level terms, you could create a date table and associate both your date columns to the same date table. Then in your DAX calculations to count number of calls you would use the "USERELATIONSHIP" function to determine which relationship to use (1 will have to remain inactive)
This blog explains it well, and gives you another option to create multiple tables, but that may mess with one x-axis
2 Replies
renanpinheiro in really high level terms, you could create a date table and associate both your date columns to the same date table. Then in your DAX calculations to count number of calls you would use the "USERELATIONSHIP" function to determine which relationship to use (1 will have to remain inactive)
This blog explains it well, and gives you another option to create multiple tables, but that may mess with one x-axis
Hi renanpinheiro,
According to your description, you want to get the monthly count of ‘DataCriacao’ and ‘DataConclusao’, right?
You can refer to below sample:
Create a test table contains Operator, DataCriacao, DataConclusao
Write the measures to calculate the monthly open and close:
Sum of Close/monthly = CALCULATE(COUNTROWS('Operator Records'),FILTER(ALL('Operator Records'),COUNTAX(FILTER('Operator Records',MONTH('Operator Records'[DataConclusao])=MONTH(EARLIER('Operator Records'[DataConclusao]))),'Operator Records'[Operator])) )
Sum of Open/monthly = CALCULATE(COUNTROWS('Operator Records'),FILTER(ALL('Operator Records'),COUNTAX(FILTER('Operator Records',MONTH('Operator Records'[DataCriacao])=MONTH(EARLIER('Operator Records'[DataCriacao]))),'Operator Records'[Operator])) )
Add then to the table visual:
In order to see the result more clearly, create two table with the specify records:
Close = DISTINCT( SELECTCOLUMNS('Operator Records',"Month",MONTH([DataConclusao]),"Count",[Sum of Close/monthly]))
Open = DISTINCT( SELECTCOLUMNS('Operator Records',"Month",MONTH([DataCriacao]),"Count",[Sum of Open/monthly]))
Regards,
Xiaoxin Sheng