Forum Discussion
ITManuel
6 years agoResponsive Resident
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 upd...
- 6 years ago
Alright.
Thanks for your help.
Br
Anonymous
6 years agoNot 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- ITManuel6 years agoResponsive 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
- Anonymous6 years agoNot applicableIf you connect the Dates table, you'll immediately see why it shouldn't be connected. Use the code as-is without any changes.
- ITManuel6 years agoResponsive Resident
Ok, done.
The progress curve with no date table connected looks like:
The progress curve with date table connected looks like:
Why is there such a difference? Also the trend turns negative into September which is not what the input data says.
Can I flatten the curve for the first case?
Br