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
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
Icon for Super User rankSuper 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).