CODEWITHSUNDEEP
powerbidax
Since_2008
Since_2008

Reputation: 2341

Sum of a measure per date range

I have the following Table Visualization.

enter image description here

I'd like the table to look like the following. Column C should be averaging the range of Column B.
For example:

C2 = AVERAGE(B2:B2)
C3 = AVERAGE(B2:B3)
C4 = AVERAGE(B2:B4)

and so on.

enter image description here

The Year-Month column is from my MonthTable. The schema is as follows,

enter image description here

And the Sum measure DAX is as follows,

enter image description here

For the CumulativeSum measure, I have tried the following.

CumulativeSum = 
CALCULATE(
[Sum],FILTER(AppendedTables,AppendedTables[Year-Month] <= MAX(AppendedTables[Year-Month]))
)

I'm guessing the issue is my CALCULATE([SUM]) area. I wanted to wrap [SUM] in a SUM() method, but that doesn't work. It gives the error "The SUM function only accepts a column reference as the argument number 1".

Please enlighten me.

Upvotes: 1

Views: 300

Answers (1)

StelioK
StelioK

Reputation: 1781

I've been able to produce your desired results by creating a calculated column using the following code:

CumSum = 
VAR CntRow =
    COUNTROWS ( FILTER ( Sheet1, [Year-Month] >= EARLIER ( [Year-Month] ) ) )
VAR CumSum =
    CALCULATE (
        SUMX ( Sheet1, Sheet1[Sum] ),
        FILTER ( Sheet1, Sheet1[Year-Month] >= EARLIER ( Sheet1[Year-Month] ) )
    )
RETURN
    DIVIDE ( CumSum, CntRow )

enter image description here

Hope this helps!!

Upvotes: 0

Related Questions