Simplification of calculation values for X months back

[Sk1X1]

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!

[v-shex-msft]

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]

Hello @v-shex-msft, 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?

[v-shex-msft]

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]

Hello @@v-shex-msft,

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

[v-shex-msft · Accepted solution]

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