DAX Progress calculation

[ITManuel]

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

[Anonymous]

// 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

[ITManuel]

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

[Anonymous]

If you connect the Dates table, you'll immediately see why it shouldn't be connected. Use the code as-is without any changes.

[ITManuel]

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

[Anonymous]

@ITManuel

There is a difference because DAX behaves differently when there are relationships between tables and when there aren't. And whether you should or not have relationships depends on what you want to achieve and what makes sense in a given situation. In this case a relationship on date makes no sense because what you're always interested in is not a cross-section through the table with documents based on the currently selected chunk of time but rather you're interested in the status of the selected documents AT THE END OF THE PERIOD IN QUESTION and this is best achieved through the disconnected Dates table.

I don't know why your graph for the connected table scenario doesn't work but as this is a case which should not arise according to what I prescribed, it's actually not too much of an interest (even though it can be precisely explained with a bit of an unnecessary effort). But I wouldn't dwell on it too much. Just use the disconnected table scenario and you're good to go.

[ITManuel]

Hi daxer,

I'm afraid, from what I see the calculated curve seems not to be correct. Even not connecting to the calendar table, there is a negative trend in the end of the curve, which is not what the data says, as there is only a positive trend in the data.

If I put "0%" in the data table before any progress has started, the negative trend is gone. But infact this does not change the progress/data.

Also one curve start above "0%" but the data does not say that.

The curves are currently like a stair, how can I get nice looking curves?

I provide the files under the link below.

https://we.tl/t-mJhErC6NJN

Sorry to bother you, and thanks again in advance.

Best regards

Manuel

[Anonymous]

OK.

I've played with this a bit. First, the graph must be stepped out of neccessity. When we calculate the progress over days, the formula grabs the progress from the Schedule table and this progress is recorded in discrete chunks and you don't have data for in-between dates. Second, the formula does not work correctly because it's much harder to write correct DAX against a bad model (which this one is). A good model is dimensional, where there are dimensions and a fact table. You only have a fact table. Proper design in Power BI is CRUCIAL to get simple and correct DAX. Third, here's the correct formula in this bad model where the Schedule fact table (as you painted it in your first post) is CONNECTED to the Dates table:

Doc Progress = 
var __lastVisibleDate = MAX( Dates[Date] )
var __selectedDocumentsWithProps =
    CALCULATETABLE(
        SUMMARIZE( 
            Schedule,
            Schedule[Document],
            Schedule[Prop]
        ),
        ALL( Dates )
    )
var __lastDateOfProgress =
    CALCULATE(
        MAX( Schedule[Date] ),
        __selectedDocumentsWithProps,
        ALL( Schedule )
    )
var __documentsWithProgress =
    GENERATEALL(
            __selectedDocumentsWithProps,
        // For each document and the last
        // visible date we have to get the
        // latest row in T. If there are
        // no rows returned, then we'll have
        // to retrieve the proportion for
        // the document anyway.
        topn(1,
            CALCULATETABLE(
                SUMMARIZE( 
                    Schedule,
                    Schedule[Progress],
                    Schedule[Date]
                ),
                Dates[Date] <= __lastVisibleDate
            ),
            Schedule[Date],
            DESC
        )
    )
var __totalProgress =
    if( __lastDateOfProgress >= MIN( Dates[Date] ),
        DIVIDE(
            SUMX(
                __documentsWithProgress,
                Schedule[Prop] * Schedule[Progress]
            ),
            SUMX(
                __documentsWithProgress,
                Schedule[Prop]
            )
        )
    )
RETURN
    __totalProgress

This formula is more complex than necessary only because the model is bad.

By the way, if you want to get a line that's not stepped, you'll have to do a lot more calculations inside the measure. Basically, if you're on the day granularity, you'll have to sense whether the day in the current context is present in Schedule or not and if it's not, you'll have to calculate linear approximation between the nearest days to the one in question that do exist in Schedule. Doable but requires a bit of effort.

[ITManuel]

Ok thank you.

Could you provide an example of how a good model should look like in this case with the dimensions as per your suggestion?

I'm really surprised how this initially very easing looking task is turning out to be much more complicated than I thought. 🙈

Br

[Anonymous]

OK.

First, here's something about correct data structures in Power BI: Guy in The Cube. You can start with it. Briefly, a good model is one that is dimensional. A dimensional model is one that has dimensions and fact table(s) and these constitute what's called "the star schema." Fact tables are connected to dimensions via keys (99% integers) and NEVER directly to each other. Slicing is done ONLY through dimensions and all columns in fact tables must be hidden. Only measures are allowed to be visible if placed in a fact table. Here's an example of a star schema.

Yeah, I know you are surprised that this seemingly easy task requires some good and deep knowledge of DAX. This is because - as I said - of the model. If I wanted to get what you want and in an easy-to-code way, I'd do something different. I would create a dimension called Document that would store unique documents and their properties (something you could then slice by). Then I'd have a Dates dimension for time (which you do already have). Then I'd have a fact table that would store DocumentID, Date and Progress (this is because fact tables for the most part record PROCESSES over time). You don't have to store the proportion for each document in there because the proportion is an attribute of a document and does not change over time, hence it does not belong to the process. One more and very important thing. If you want to see progress through time as a non-stepped line, the best way is to store progress of each document through time in the fact table on the day granularity. So, each day for each document you should record the progress. If you do that, DAX will be simple and you'll get the "smooth" line that you've been dreaming of :)))

[ITManuel · Accepted solution]

Alright.

Thanks for your help.

Br

[ITManuel]

Also tried to do comparison in between target and real progress. 

Strangely I have a negative trend on the real curve and it does not start at "0" with the following data

PDT=ProgressDesignTarget

PDR=ProgessDesignReal

Best regards