Forum Discussion

renanpinheiro's avatar
renanpinheiro
Icon for Advocate II rankAdvocate II
9 years ago
Solved

Generic 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 th...
  • Anonymous's avatar
    Anonymous
    9 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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