Cumulative progress calculation using discrete updates to progress

[Ammii11]

Hi,

I am new to Power BI and do not have a particularly strong coding background.

I am trying to display a project's progress (in terms of hours) over time based on earned value (in hours) for submission of documents. The only issue is, when a document is submitted for the same purpose more than once, I do not want to double count the document. Please see below.

Document Name	Issue Purpose	Progress Earnt (%)	Total for this document (hrs)	Date
DocA	IFR	20	10	May 1st 2023
DocB	IFR	20	10	May 20th 2023
DocC	IFR	20	10	May 20th 2023
DocA	IFC	90	10	June 2nd 2023
DocB	IFC	90	10	June 4th 2023
DocC	IFC	90	10	June 5th 2023
DocA	IFC	90	10	June 19th 2023
DocB	IFC	90	10	June 19th 2023

My dataset is a history of all the submissions of each of the documents, with a new row for each submission.

Then the cummulative progress becomes as follows (note I've not included all the inbetween dates though my visual will be a date continuum).

Date	20th April 2023	May 1st 2023	May 21st 2023	June 2nd 2023	June 4th 2023	June 5th 2023	June 19th 2023
Progress (hrs)	0	2	6	13	20	27	27

In this case although DocA and DocB were issued twice, it was for the same issue purpose so they do not earn any more progress hours. 

Any help would be greatly appreciated. So far, I have not be able to make any useful advances in getting this accomplished.

Thanks,
Amy

[amitchandak]

@Ammii11 , You have try meausre like

Sumx(SUMMARIZE(Table, Table[Document Name], Table[Issue Purpose], Table[document (hrs)]), Table[document (hrs)])

Or create a column
UniqueDocument = CONCATENATE([Document Name], [Issue Purpose])

and a measure like

Cumm =
CALCULATE(
SUM('Table'[Progress Earnt (%)]);
FILTER(
ALL('Table');
'Table'[UniqueDocument] IN VALUES('Table'[UniqueDocument]) &&
'Table'[Date] <= MAX('Table'[Date])
)
)

[Ammii11 · Accepted solution]

For anyone out there interested, I ended up getting the result I was looking for using the following:

measure = calculate(sumx(DISTINCT(table[document_number])),filter(allselected(table[document_received_date]),sheet[document_received_date]<=max(sheet[document_received_date])))

Where document number is constructed such that future revisions have the same numbering and thus are not distinct.

Note that for a given day the total will be shown but the cummulative sum avoids including the previous versions in the count.

[Ammii11]

Hi @amitchandak - thanks for this. It does seem to go partly where I need to be. However, I am still having some issues. I have tried both methods. With the following output.

The actual for this date should be 20.56 as the progress on the other docs in yellow was already earnt.

I've used this for the table on the left

CummProgDocs = CALCULATE(

    SUM(SDX_Doc_History[Hours_Earn]),

    FILTER(

        ALL(SDX_Doc_History),

       SDX_Doc_History[UniqueDocument] IN VALUES(SDX_Doc_History[UniqueDocument]) &&

        SDX_Doc_History[Formally Received] <= MAX(SDX_Doc_History[Formally Received])

    )

)

and this for that on the right. Please can you advise if you see any errors in my interpretation?

Doc_Prog = sumx(SUMMARIZE(SDX_Doc_History,SDX_Doc_History[document title],SDX_Doc_History[Issue Purpose],SDX_Doc_History[Hours_Earn]),SDX_Doc_History[Hours_Earn])

Thanks again.