Forum Discussion

Sk1X1's avatar
Sk1X1
Frequent Visitor
8 years ago
Solved

Simplification of calculation values for X months back

Helo guys,
I want to calculate duration of tasks for every month last year. Now I ended with 12 calculated tables. Every table is calculated from source table and filter tasks, that are not created yet in specific month.
For January I take only tasks that are created until 31.1.2017:

 

_1 = CALCULATETABLE(SELECTCOLUMNS(DevBugs;"ID";DevBugs[ID];"Created";DevBugs[Created];"Finished";DevBugs[Finished]);FILTER(DevBugs;DevBugs[Created]<DATE(2017;1;31));FILTER(DevBugs;DevBugs[Finished] >= DevBugs[Datum vytvoření]))

 

For February same, but 28.2.2017:

 

_2 = CALCULATETABLE(SELECTCOLUMNS(DevBugs;"ID";DevBugs[ID];"Created";DevBugs[Created];"Finished";DevBugs[Finished]);FILTER(DevBugs;DevBugs[Created]<DATE(2017;2;28));FILTER(DevBugs;DevBugs[Finished] >= DevBugs[Created]))

and etc for other months.

 

In every one of this table I have calculated column that calculated. For example in January table:

Duration = if(_1[Finihed] <= DATE(2017;01;31);DATEDIFF(_1[Created];_1[Finished];DAY);DATEDIFF(_1[Created];DATE(2017;01;31);DAY))

 

And at the end I have final table, which use UNION to combine data from previous table and calculate AVG for every month. And I'm wondering if it is some simplier way how to achieve same result, but for example without 12 calculated tables.

Thanks for any advice!

 

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    HI Sk1X1,

     

    I think you can add task column to formula as one of group columns, please try to use below formula if it works for your scenario.

     

    Calculate Tables =
CALCULATETABLE (
    SELECTCOLUMNS (
        DevBugs;
        "ID"; DevBugs[ID];
        "Tasks"; DevBugs[Tasks];
        "Created"; DevBugs[Created];
        "Finished"; DevBugs[Finished]
    );
    FILTER (
        DevBugs;
        DevBugs[Created]
            < DATE ( YEAR ( DevBugs[Created] ); MONTH ( DevBugs[Created] ) + 1; 1 )
                - 1
    );
    FILTER ( DevBugs; DevBugs[Finished] >= DevBugs[Datum vytvoření] )
)

     

    Regards,

    Xiaoxin Sheng

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    HI Sk1X1,

     

    I'd like to suggest you use below formula to create one table with summary records:

    Calculate Tables =
CALCULATETABLE (
    SELECTCOLUMNS (
        DevBugs;
        "ID"; DevBugs[ID];
        "Created"; DevBugs[Created];
        "Finished"; DevBugs[Finished]
    );
    FILTER (
        DevBugs;
        DevBugs[Created]
            < DATE ( YEAR ( DevBugs[Created] ); MONTH ( DevBugs[Created] ) + 1; 1 )
                - 1
    );
    FILTER ( DevBugs; DevBugs[Finished] >= DevBugs[Datum vytvoření] )
)

    Then use above table to calculate durations.

     

    Regards,

    Xiaoxin Sheng

    • Sk1X1's avatar
      Sk1X1
      Frequent Visitor
      8 years ago

      Hello Anonymous, thank you for your reply.

       

      I tried create calculate table with your advice. Sadly, the result is not same as is my summary table. Here is screenshot to compare 

       

      With my approach, task will be in result for every month from it's creation. So I can add it into AVG calculation for every month. Is here way to get this behavior. I mean to expand one row into more rows?

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Hi Sk1X1,

         

        >>Sadly, the result is not same as is my summary table.

        Normally, SUMMARIZE function will group table records by specific columns.

         

        >>So I can add it into AVG calculation for every month.

        Maybe you can modify my formula to add count column to store current duplicate records count used to deal with ave calculation.(in my opinion, not need to keep duplicate records, summarized duplicate records equal to averaged records)

         

        Can you please share more detail contents? (e.g. sample data, screenshots, expected result...)

         

        Regards,

        Xiaoxin Sheng