Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Trending Graph for a Date Calculated Measure

Hello, what I want to get is a line visual that shows 'Activity Health' over the last year.

 

Activity Heath is calculated from three other fields (items opened divided by the combination of items closed and cancelled). To get these components there is an Initiation Date, a Closed Date, and a Cancelled Date. Keep in mind that not all items have all these fields since not all are cancelled or closed. I've been fighting for a way to not only calculate this for the previous month, but also for showing the trend over the last year.

 

Help!

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous 

     

    As per my understanding you can create below new columns and measure to retreive ActivityHealth across Each Month-Year.
    Column 1

    MonthYear = LEFT(FORMAT('Table'[OpenDate],"MMM"),3) & " - " & Convert(RIGHT('Table'[OpenDate],4),STRING)
    Column 2 (Denominator)
    ClosedCancelItems = 
    If( ISBLANK('Table'[CancelDate]) , 0 , 1) + If( ISBLANK('Table'[ClosedDate]) , 0 , 1)
    Measure 😞 Assuming Opendate will not be null/blank)
    ActivityHealth = IFERROR( COUNT('Table'[OpenDate]) / SUM('Table'[ClosedCancelItems]) , 0).

     

    If it's not working . Please share the sample PBIX of your requirement so that it might help me to provide proper DAX formulas.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    hi Anonymous ,

     

    So your requirement is to calculate No.of Items/(No.of Items Cancelled + Closed) per a month -year(MMM-YY)?

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      The activity health for a given month is equal to the number of items created that month divided by the sum of the items closed and cancelled that month. Then I want to be able to trend that over the last year. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi Anonymous 

         

        As per my understanding you can create below new columns and measure to retreive ActivityHealth across Each Month-Year.
        Column 1

        MonthYear = LEFT(FORMAT('Table'[OpenDate],"MMM"),3) & " - " & Convert(RIGHT('Table'[OpenDate],4),STRING)
        Column 2 (Denominator)
        ClosedCancelItems = 
        If( ISBLANK('Table'[CancelDate]) , 0 , 1) + If( ISBLANK('Table'[ClosedDate]) , 0 , 1)
        Measure 😞 Assuming Opendate will not be null/blank)
        ActivityHealth = IFERROR( COUNT('Table'[OpenDate]) / SUM('Table'[ClosedCancelItems]) , 0).

         

        If it's not working . Please share the sample PBIX of your requirement so that it might help me to provide proper DAX formulas.