cancel
Showing results for
Did you mean:
Regular Visitor

## Showing sum of 3 months back for the next two years

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

1 ACCEPTED SOLUTION
Microsoft

Hi， @trebor2mng

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 (
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?

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.

4 REPLIES 4
Regular Visitor

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

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

Regular Visitor

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.

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

Microsoft

Hi， @trebor2mng

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 (
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?

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.

Microsoft

Hi, @trebor2mng

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

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.

Announcements

#### Happy New Year from Power BI

This is a must watch for a message from Power BI!