Forum Discussion
DAX Progress calculation
Hi to everybody,
I would like to calculate and visualize a progress curve showing the design progress in this case.
Data base is the following excel Matrix in which the design progress is updated manually.
The design progress curve should show the total calulcated progress per each end of month considering each design document "D1 ....D7", its progress and its related proportion/weight.
There should only be one cruve in the end.
I have unpivoted this table in Power Bi.
Now I'm looking for the right DAX to do this which I was not able so far.
Could somebody please help.
Thanks in advance
Best regards
Alright.
Thanks for your help.
Br
11 Replies
- AnonymousNot applicable
// Assumptions: // You have a Dates table in your model that // stores all days that cover all full years // that are present in your table. This is // the calendar in your model (marked as such). // This calendar must be DISCONNECTED from // the fact table. Say your fact table is T. // Apart from the Document field in T all the // other fields should be hidden. You should not // slice by them. // The measure will tell you what the percentage // of work has been completed until the last // day visible in the current context. This // measure works for any selection of documents // and is relative to this selection. [Progress] = var __lastVisibleDate = MAX( Dates[Date] ) var __documentsWithLastDates = GENERATE( SELECTCOLUMNS( VALUES( T[Document] ), "@Document", T[Document] ), // For each document and the last // visible date we have to get the // latest row in T. topn(1, CALCULATETABLE( T, T[Date] <= __lastVisibleDate ), T[Date], DESC ) ) var __totalProgress = DIVIDE( SUMX( __documentsWithLastDates, T[Proportion] * T[Progress Target] ), SUMX( __documentsWithLastDates, T[Proportion] ) ) RETURN __totalProgress- ITManuelResponsive Resident
Hi Daxer,
thanks for your quick respone.
I do have a date/calendar table, could you please explaind why this shouldn't be connected to the fact table?
Also I don't understand the "@Document" part in the Values function. What do I have to insert here?
Thank you
Best regards
- AnonymousNot applicableIf you connect the Dates table, you'll immediately see why it shouldn't be connected. Use the code as-is without any changes.