cancel
Showing results for
Did you mean:
KarlConstruct Regular Visitor

## Trend Chart (Latest Date vs Last Run Date)

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!

1 ACCEPTED SOLUTION

Accepted Solutions v-jayw-msft
Microsoft

## Re: Trend Chart (Latest Date vs Last Run Date)

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. v-jayw-msft
Microsoft

## Re: Trend Chart (Latest Date vs Last Run Date)

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.

Announcements #### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system. #### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge. #### January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January! Top Solution Authors
Top Kudoed Authors
Users online (5,613)