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

 

 

Need Help

4 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper 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

      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

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