Measures to calculate Min Date, Max, Date, and Averages if Open or Closed

[Anonymous]

Hi,

I have a bunch of projects where I am trying to get a table with average days to close and days since opened to today and ignoring any zeroes.

My Data and results look like this and Start Date is linked to  Calendar Table. 

Issue Key	Open or Closed	Start Date	Resolution Date	Today	Days Open	Days To Done
PLATTEAM-1820	Open	4/1/2024		9/25/2024	177	0
PLATTEAM-1820	Open	5/1/2024		9/25/2024	147	0
PLATTEAM-1820	Closed	6/1/2024	9/1/2024	9/25/2024	0	92
PLATTEAM-2110	Closed	7/15/2024	8/15/2024	9/25/2024	0	31
PLATTEAM-2110	Open	9/10/2024		9/25/2024	15	0
	Average Excluding Zero		113	61.5

I am using 

Start Date= CALCULATE(MIN(vwJiraEpicTimeStates[CreatedOnNext]),ALLEXCEPT(vwJiraEpicTimeStates,vwJiraEpicTimeStates[IssueKey]))

Resolution Date= CALCULATE(MAX(vwJiraEpicTimeStates[Resolution Date ]),ALLEXCEPT(vwJiraEpicTimeStates,vwJiraEpicTimeStates[IssueKey])

And I am using Two Measures with Date diff to get days to done and days open where appropriate but I cannot get the subtotals to work the measures. I would like to avoid having extra columns due to the size of the file .  

Is there any way to accomplish this using only measures?  Thanks!

[Greg_Deckler]

@Anonymous First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

[Anonymous]

Thanks!

Two quick follow up questions

1) Basically the way I am interperting your response is that for every other column in the table, you would need to add that into the temp tabl  For instance, if you had 7 columns you would have to have summarize it by every category and tweak it every time you add or remove a column?

m_Total 4 =

VAR __table = SUMMARIZE('Table1',[Category1],[Category2],"__value",[m_Single])

RETURN

IF(HASONEVALUE(Table1[Category1]),[m_Single],SUMX(__table,[__value]))

2) If you really wanted to take any of those measure calculations and ignore any zeroes, how would that work?
Thanks