Trending Graph for a Date Calculated Measure

[Anonymous]

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!

[Anonymous]

hi @Anonymous ,

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

[Anonymous]

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 · Accepted solution]

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.

[amitchandak]

@Anonymous ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

refer if this blog, how to deal with multiple dates can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970