Trend Chart (Latest Date vs Last Run Date)

[KarlConstruct]

Hello All, 

I wanted to perform a trend analysis on a spreadsheet that gets generated on a weekly basis. Currently this is being done manually and I wanted to automate this with PowerBI. 

First here are the results I am trying to achieve. 

- Show current number of "Open" Change Estimates (CE's) if category is "Internal". 

- Show # of "Internal" CE's that have been opened/created since report was last run. 

- Show # of "Internal" CE's that have been closed since report was last run. 

• Show a trend line visual of these 3 items listed above. 

When calculated manually it looks like this. 

I have 3 tables of data available to use. 

- Date Table (Date/Day/Month/Week Number/Week Start Date/Week End Date)

- Change Estimate History (shows when reports were run in first column) 

- Change Estimate Summary (no history, just current state of CE's) 

Can anyone get me started in the right direction please? Any help would be greatly appreciated!

[v-jayw-msft · Accepted solution]

Hi @KarlConstruct ,

If i understand you correctly, CALCULATEDTABLE() and EXCEPT() functions might be helpful for you.

Below is a simple example i made, please refer to the measures and check the result.

CurrentOpened = CALCULATE(DISTINCTCOUNT('Table'[CE]),FILTER('Table','Table'[Category]="internal"&&'Table'[IsClosed]=0))

opened = 
var a = CALCULATETABLE(DISTINCT('Table'[CE]),FILTER('Table','Table'[Category]="internal"&&'Table'[IsClosed]=0))
var b = CALCULATETABLE(DISTINCT('Table'[CE]),FILTER(ALL('Table'),'Table'[Category]="internal"&&'Table'[IsClosed]=0&&'Table'[Run date]=SELECTEDVALUE('Table'[Run date])-1))
return
COUNTROWS(EXCEPT(a,b))

closed = 
var a = CALCULATETABLE(DISTINCT('Table'[CE]),FILTER('Table','Table'[Category]="internal"&&'Table'[IsClosed]=1))
var b = CALCULATETABLE(DISTINCT('Table'[CE]),FILTER(ALL('Table'),'Table'[Category]="internal"&&'Table'[IsClosed]=1&&'Table'[Run date]=SELECTEDVALUE('Table'[Run date])-1))
return
COUNTROWS(EXCEPT(a,b))

Pbix as attached.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.