Forum Discussion

ITManuel's avatar
ITManuel
Responsive Resident
5 years ago
Solved

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

11 Replies

  • Anonymous's avatar
    Anonymous
    Not 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
    • ITManuel's avatar
      ITManuel
      Responsive 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

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