cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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))
 

Capture.PNG

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

 

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

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 (
    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]
)

1.png

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.

View solution in original post

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.

This is your pbix:

Capture.PNG

 and what exactly I would like to see is:

 

Capture2.PNG

 

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

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 (
    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]
)

1.png

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.

View solution in original post

Microsoft
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 (
    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]
)

2.png

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.

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors