TRICKY - Diagonal Sum to Sum a Range of Data

[Anonymous]

Hi Experts

I am trying to sum the range of data as shown in the image below.

The Sum only applies to the last for Periods as Shown in the image.. 

See attached PBIX where i can add single cell - how do you sum a range of cell as shown above

Sample PBIX

https://www.dropbox.com/s/bko95z5uckgj9j3/sample%283%29.pbix?dl=0 

[CNENFRNL · Accepted solution]

Tricky Sum = 
VAR __inteval = DATESINPERIOD( DATES[Date], MAX( DATES[Date] ), -3, MONTH )
RETURN
    IF(
        MIN( DATES[Date] )
            >= EDATE( CALCULATE( MIN( DATES[Date] ), ALL() ), COUNTROWS( SPAN ) + 1 ),
        SUMX(
            DISTINCT( SPAN[Inteval] ),
            VAR __i = SPAN[Inteval]
            RETURN
                CALCULATE(
                    SUM( 'SAMPLE'[Value] ),
                    'SAMPLE'[Attribute] = __i,
                    DATEADD( __inteval, - SPAN[Inteval], MONTH )
                )
        )
    )

[Anonymous]

Hi CNENFRNL..... How would you do the following - since you original solution was stop on
https://community.powerbi.com/t5/Desktop/TRICKY-Diagonal-Sum-to-Sum-a-Range-of-Data/m-p/1939347#M738...

[Anonymous]

Hi CNENFRNL.... could you kindly have a look at the following please..... stuck and unable to find a solution. 

https://community.powerbi.com/t5/Desktop/TRICKY-Average-last-6-5-4-and-3-months/m-p/1936148#M737666

[Anonymous]

Many Thanks to all experts

[parry2k]

@Anonymous interesting, solution attached.

Sum by Attribute = 
CALCULATE ( 
    SUM ( 'Sample Data'[Value] ), 
    DATESINPERIOD ( 
        'Calendar'[Date], 
        EOMONTH ( 
            MAX ( 'Calendar'[Date] ), 
            MAX ( 'Sample Data'[Attribute] ) * -1
        ),
        -3, 
        MONTH 
    ) 
)


Measure 2 = 
SUMX ( 
    SUMMARIZE ( 
        'Sample Data', 
        'Calendar'[Month], 
        'Sample Data'[Attribute] 
    ), 
    [Sum by Attribute] 
)

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k]

@Anonymous is this the expected output?

[Anonymous]

Hi Parry

The expected output would be a simple table like this as similir to the table in the PBIX File,

Feb-20
Mar-20
Apr-20
May-20
Jun-20
Jul-20
Aug-20
Sept-20
Oct-20	147.2
Nov-20	152.9
Dec-20	148.2
Jan-21	162.5

The ranges highlighted and the sum formula gives Oct-20.