Forum Discussion

Ammii11's avatar
Ammii11
New Member
3 years ago
Solved

Cumulative progress calculation using discrete updates to progress

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 subm...
Need Help
  • Ammii11's avatar
    Ammii11
    3 years ago

    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.

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
3 years ago

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's avatar
Ammii11
New Member
3 years ago

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.