Forum Discussion
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!
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.
1 Reply
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.