Forum Discussion

zhona9's avatar
zhona9
Icon for Resolver I rankResolver I
3 years ago
Solved

Column chart with two different date for the same month

I would like to make a stacked column chart that reports the number of incidents closed in a month, basis the incident sign-off date and the number of incidents reported in a month, basis the inciden...
  • johnt75's avatar
    johnt75
    3 years ago

    Create 2 relationships from your date table to your fact table, one based on the sign off date and one based on the reported date. You can then activate the correct relationship in the measures like

    Num reported =
CALCULATE (
    COUNTROWS ( 'Table' ),
    USERELATIONSHIP ( 'Date'[Date], 'Table'[Reported date] )
)
johnt75's avatar
johnt75
Icon for Super User rankSuper User
3 years ago

Create 2 relationships from your date table to your fact table, one based on the sign off date and one based on the reported date. You can then activate the correct relationship in the measures like

Num reported =
CALCULATE (
    COUNTROWS ( 'Table' ),
    USERELATIONSHIP ( 'Date'[Date], 'Table'[Reported date] )
)
  • zhona9's avatar
    zhona9
    Icon for Resolver I rankResolver I
    3 years ago

    Do you have a link to how to create a date table? 

     

    I found this code, but not sure if it is correct.

     

    Dates = CALENDAR(MIN('g360_Incidents'[reported_date]),MAX('g360_Incidents'[risk_lead_sign_off_date]))

     

    I get an error:

     

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

      https://bravo.bi/ can create date tables for you. The syntax you used is correct, not sure why you're getting an error. Maybe some of the dates in the columns you referenced are blank.

      • zhona9's avatar
        zhona9
        Icon for Resolver I rankResolver I
        3 years ago

        In the end I used the reported date for min and max in the date table and it was ok. You are right, the sign off date will be blank at times and that caused the error.