Creating a chart with 2 dates to comapre entry and exit

[PTIFANOU]

Hi all, I am a complete newbe to PowerBI.

I have a table with 3 columns, the first one is "id file" each name is different, the second is the date of creation for each of this files,the third one is the date of cloture.

i'd like to make a bar chart that shows by month the number of files created (one bar) and the number of files finished (second bar)

thanks, and sorry for my english

[Greg_Deckler · Accepted solution]

@PTIFANOU OK:

• Create a disconnected table for your months (no relationship) using an Enter Data query to just enter your month names in one column and the month number of the year (1...12) in the other column. Let's assume that you call this table "Months" and the columns are "Month" and "MonthSort" respectively
• Set the Sort By column for the first column to be your second column
• Create the following 2 DAX calculated columns in your table:

Creation Month = FORMAT([CreationDate],"mmmm")


Cloture Month = FORMAT([ClotureDaate],"mmmm")

• Create the following 2 measures:

Documents Created = 
  VAR __Month = MAX('Months',[Month])
RETURN
  COUNTROWS(FILTER('Table',[Creation Month] = __Month))


Documents Cloture = 
  VAR __Month = MAX('Months',[Month])
RETURN
  COUNTROWS(FILTER('Table',[Cloture Month] = __Month))

• Create a bar chart visual
• Place Month column from your Months table into the X-axis field well
• Place the two measures into the Y-axis field well

[Jihwan_Kim · Accepted solution]

Hi,

I suggest having a calendar table like below.

One active relationship and one inactive relationship.

And then use USERELATIONSHIP dax function when using inactive relationship.

Start count: =

COUNTROWS( Data )

End count =

CALCULATE( [Start count:],

    USERELATIONSHIP('Calendar'[Date], Data[EndDate])

)

[Greg_Deckler · Accepted solution]

@PTIFANOU OK:

• Create a disconnected table for your months (no relationship) using an Enter Data query to just enter your month names in one column and the month number of the year (1...12) in the other column. Let's assume that you call this table "Months" and the columns are "Month" and "MonthSort" respectively
• Set the Sort By column for the first column to be your second column
• Create the following 2 DAX calculated columns in your table:

Creation Month = FORMAT([CreationDate],"mmmm")


Cloture Month = FORMAT([ClotureDaate],"mmmm")

• Create the following 2 measures:

Documents Created = 
  VAR __Month = MAX('Months',[Month])
RETURN
  COUNTROWS(FILTER('Table',[Creation Month] = __Month))


Documents Cloture = 
  VAR __Month = MAX('Months',[Month])
RETURN
  COUNTROWS(FILTER('Table',[Cloture Month] = __Month))

• Create a bar chart visual
• Place Month column from your Months table into the X-axis field well
• Place the two measures into the Y-axis field well

[PTIFANOU]

thanks a lot both of yours solutions works perfectly

thanks fotr ur time and efforts