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

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

    • Sk1X1's avatar
      Sk1X1
      Frequent Visitor
      8 years ago

      Hello @Anonymous,

      I will try to show you example of my approach.

       

      This is like my source data looks like:

       

       When I use first formula:

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

       then I will get new table where I add new column:

       

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

      And result is table _1 with this content:

       

      This result represents task durations until January.

      Then I will do the same for Februar. Only change in formula is the date. From 31.1.2018 to 28.2.2018. Result will be:

       

      With these tables I'm able to create AVG of Task Duration for two months. For January it would be 15,7 and for February it would be 14.

       

      But I would be like to use only one formula to create these result. For now I have to create 12 tables. 
      I hope you will understand better now

      • Anonymous's avatar
        Anonymous
        Not applicable
        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