Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Monthly progress representation from annual objective

Hello I need help to make a monthly progress representation I have 4 tables containing the records of 4 forms (Date, Site, Name, etc...) I have 4 annual objectives for these 4 forms of the type "i...
Need Help
MFelix's avatar
MFelix
Super User
5 years ago

Hi Anonymous ,

 

If your 4 tables are the same I believe that the best option is not to have 4 different tables but to append them together and have a single table with all the values with a column identifing the form.

 

If you have single table for the objectives that need to be divided by month believe the best option is to make a relationship between the services and form (not by date) and then using a simple measure like SUM(Table[Objective])/12 you would get the monthly total.

 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

MFelix 

taking into account your remark, I built a new table which compiles the common data of the 4 others
Below are the working data now, but I'm not sure of the right strategy for my monthly breakdown Achieved / Goal

Both tables (those below) are filtered by calendar table and another one (for activities perimeter)

Should i divide annual objective per number of days in the year ? and then a measure with totalytd ?

Common table

Objectiv Table

 

  • MFelix's avatar
    MFelix
    Super User
    5 years ago

    Hi Anonymous ,

     

    Looking at your data I would not relate the objective table with the calendar and then would create the measures similar to this ones:

    Daily_Objective =
CALCULATE (
    SUM ( 'Objectives'[Annual Objective] ) / 365;
    FILTER (
        'Objectives';
        YEAR ( 'Objectives'[YearConcerned] )
            = YEAR ( SELECTEDVALUE ( 'calendar'[Date] ) )
    )
)

Cumulative Objective =
[Daily_Objective]
    * COUNTROWS (
        FILTER (
            ALL ( 'calendar' );
            'calendar'[Date] >= DATE ( YEAR ( SELECTEDVALUE ( 'calendar'[Date] ) ); 1; 1 )
                && 'calendar'[Date] <= SELECTEDVALUE ( 'calendar'[Date] )
        )
    )

     

    Then you can use that to make other calculations

    check PBIX file attach (October2020 version of PBI).