Showing sum of 3 months back for the next two years

[Anonymous]

Hello guys, 

I am stuck and would like to ask for some help.

So, what I would like to do? Get the sum of past three months and show it for the future months. It's tricky becasue I would like to refer to the sum of August, September and October always (as we are in November).

So let me show you 2 tables with "Value 3 months back" formula which looks like this:

P3M Value = CALCULATE('Table'[Value],FILTER(ALL('Table'[Date Difference]),'Table'[Date Difference]>=-3 && 'Table'[Date Difference] <0))

And what I would like to achieve? Let me show it in exactly in excel.

Q1 is 07/01/2020, 08/01/2020, 09/01/2020

Q2 is 10,11,12/2020

Q3 is 01,02,03/2020

Q4 is 04,05,06/2020

I've tried a lot of things and it works perfectly while taking 3 months from the time that we're refering to, so then this "Value 3 months back" is changing. I would like it to be constant across next two fiscal years and be based on the month that we are currently in.

I hope that I explained it in a way that is understandable.

Best regards,

TT

[Anonymous]

Thank you very much for your help. It works as expected.

P.S. Yeah, I've considered it 🙂

[Anonymous]

Hi,

@v-janeyg-msft thank you for your answer. I appreciate, however this is not the result that I've expected.

This logic I can achieve by a formula of that kind:

CALCULATE( SUMX('Table',IF(DATEDIFF('Table'[Date],TODAY(),MONTH) <=3 && DATEDIFF('Table'[Date],TODAY(),MONTH) >=1, 'Table'[Value],0))).

What I would like to show and I see it tricky is not moving value of last 3 months.

This is your pbix:

 and what exactly I would like to see is:

So the value of August is 67, September 88 and October 77. It gives us a Total of 232.

For the monthly table I would like to have it divided by 3 (77.33) and spread across whole table:

77.33 for Nov, Dec, Jan 2021 and so on. Always refer to the past 3 months and then calculate a difference.

For the quarterly table I would like to always refer to 232 value for Q2, Q3, Q4.

Thank you!

Regards,

TT

[v-janeyg-msft · Accepted solution]

Hi， @Anonymous 

You can try this in my previous sample file:

Measure = 
SUMX (
    SUMMARIZE (
        Table1,
        [Date],
        [Value],
        [Index],
        "threemonthback",
            VAR a =
                SUMX ( FILTER ( ALL ( Table1 ), [Index] = 1 ), [Value] )
            VAR b =
                SUMX ( FILTER ( ALL ( Table1 ), [Index] = 2 ), [Value] )
            VAR c =
                SUMX ( FILTER ( ALL ( Table1 ), [Index] = 3 ), [Value] )
            VAR d = 
                ( b + c + a ) / 3
            var e =
                MAXX(FILTER(ALL(Table1),[Date]=SELECTEDVALUE(Table1[Date])),[Value])
            RETURN
                IF ( SELECTEDVALUE ( Table1[Index] ) < 3, e, d )
    ),
    [threemonthback]
)

Difference = 
SUMX (
    ADDCOLUMNS (
        SUMMARIZE (
            Table1,
            [Date],
            [Value],
            [Index],
            "threemonthback",
            VAR a =
                SUMX ( FILTER ( ALL ( Table1 ), [Index] = 1 ), [Value] )
            VAR b =
                SUMX ( FILTER ( ALL ( Table1 ), [Index] = 2 ), [Value] )
            VAR c =
                SUMX ( FILTER ( ALL ( Table1 ), [Index] = 3 ), [Value] )
            VAR d = 
                ( b + c + a ) / 3
            var e =
                MAXX(FILTER(ALL(Table1),[Date]=SELECTEDVALUE(Table1[Date])),[Value])
            RETURN
                IF ( SELECTEDVALUE ( Table1[Index] ) < 3, e, d )
        ),
        "difference", [threemonthback] - [Value]
    ),
    [difference]
)

Measure 2 = 
SUMX (
    SUMMARIZE (
        Table1,
        [Value],
        [Index],
        "threemonthback",
            VAR a =
                SUMX ( FILTER ( ALL ( Table1 ), [Index] = 1 ), [Value] )
            VAR b =
                SUMX ( FILTER ( ALL ( Table1 ), [Index] = 2 ), [Value] )
            VAR c =
                SUMX ( FILTER ( ALL ( Table1 ), [Index] = 3 ), [Value] )
            return 
                ( b + c + a ) / 3

    ),
    [threemonthback]
)

By the way,You want to calculate the sum of 8,9,10, but defined 7,8,9 as a quarter. Have you considered this?

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-janeyg-msft]

Hi, @Anonymous 

It’s my pleasure to answer for you.

According to your description,I think you can create a index column in pq first,then create two measures to calculate the desired results.

Like this:

Value 3 months back = 
SUMX (
    SUMMARIZE (
        Table1,
        [Date],
        [Value],
        [Index],
        "threemonthback",
            VAR a =
                MAXX (
                    FILTER ( ALL ( Table1 ), [Date] = SELECTEDVALUE ( Table1[Date] ) ),
                    [Index]
                )
            VAR aa =
                SUMX ( FILTER ( ALL ( Table1 ), [Index] = a ), [Value] )
            VAR b =
                SUMX ( FILTER ( ALL ( Table1 ), [Index] = a - 1 ), [Value] )
            VAR c =
                SUMX ( FILTER ( ALL ( Table1 ), [Index] = a - 2 ), [Value] )
            VAR d =
                SUMX ( FILTER ( ALL ( Table1 ), [Index] = a - 3 ), [Value] )
            VAR e = ( b + c + d ) / 3
            RETURN
                IF ( SELECTEDVALUE ( Table1[Index] ) < 3, aa, e )
    ),
    [threemonthback]
)

Difference = 
SUMX (
    ADDCOLUMNS (
        SUMMARIZE (
            Table1,
            [Date],
            [Value],
            [Index],
            "threemonthback",
                VAR a =
                    MAXX (
                        FILTER ( ALL ( Table1 ), [Date] = SELECTEDVALUE ( Table1[Date] ) ),
                        [Index]
                    )
                VAR aa =
                    SUMX ( FILTER ( ALL ( Table1 ), [Index] = a ), [Value] )
                VAR b =
                    SUMX ( FILTER ( ALL ( Table1 ), [Index] = a - 1 ), [Value] )
                VAR c =
                    SUMX ( FILTER ( ALL ( Table1 ), [Index] = a - 2 ), [Value] )
                VAR d =
                    SUMX ( FILTER ( ALL ( Table1 ), [Index] = a - 3 ), [Value] )
                VAR e = ( b + c + d ) / 3
                RETURN
                    IF ( SELECTEDVALUE ( Table1[Index] ) < 3, aa, e )
        ),
        "difference", [threemonthback] - [Value]
    ),
    [difference]
)

Here is my sample .pbix file.Hope it helps.

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.