cancel
Showing results for
Did you mean:
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
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.

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