cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KarlConstruct Regular Visitor
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. 

Internal CE trend.png

 

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) 

CE Trend Table.png

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

CE Summary.png

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft v-jayw-msft
Microsoft

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

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))

 

1.PNG

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.

View solution in original post

1 REPLY 1
Microsoft v-jayw-msft
Microsoft

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

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))

 

1.PNG

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.

View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

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?

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

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors