Forum Discussion

PTIFANOU's avatar
PTIFANOU
Regular Visitor
3 years ago
Solved

Creating a chart with 2 dates to comapre entry and exit

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

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    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's avatar
    Jihwan_Kim
    3 years ago

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

     

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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's avatar
      PTIFANOU
      Regular Visitor
      3 years ago

      thanks a lot both of yours solutions works perfectly

      thanks fotr ur time and efforts

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

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