cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sk1X1 Frequent Visitor
Frequent Visitor

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!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Simplification of calculation values for X months back

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
5 REPLIES 5
Community Support Team
Community Support Team

Re: Simplification of calculation values for X months back

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
Sk1X1 Frequent Visitor
Frequent Visitor

Re: Simplification of calculation values for X months back

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 

2018-01-11.png

 

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?

 

Community Support Team
Community Support Team

Re: Simplification of calculation values for X months back

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
Sk1X1 Frequent Visitor
Frequent Visitor

Re: Simplification of calculation values for X months back

Hello @@v-shex-msft,

I will try to show you example of my approach.

 

This is like my source data looks like:

 2018-01-18 (1).png

 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:

 

2018-01-18 (3).png

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:

2018-01-18 (5).png

 

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

Community Support Team
Community Support Team

Re: Simplification of calculation values for X months back

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |