Monthly progress representation from annual objective

[ptortel]

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 "in 2020, there must be 240 forms n°1 recorded".

the aim is to see on a PBI visualization, the number achieved vs. the objective brought back to the month, which would give for example for form n°1, X / 180 at the end of September.

Having 4 separate data tables, I had started on a 5th table with the objectives per form, per year and per service but I can't make it work. I have the impression that the theoretical objective should be cut out again for each line of the 4 tables. (divide the year by the number of days, and paste this value)

Can someone tell me if this is possible with a dedicated table? If so, how?

Thanks

Table for one of the forms "VS" (First columns)

Table for objectives

[MFelix]

Hi @ptortel ,

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.

[ptortel]

@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]

Hi @ptortel ,

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).

[ptortel]

thanks for reply @MFelix 

Actually, screenshots doesn't show all columns (12-15 kept per form), and some others are different (4 forms , 4 different uses)

so, can't aggregate data tables

I'll try your option for objectives and let you know