Calculate cumulative return

[Anonymous]

Hi, I already have a measure which is calculating a profit. Now I can to create a new measure which will be cumulative profit from the last months.

	A	B	C
1		Measure	New Measure
2	January	500	500
3	February	10	510
4	March	600	1110
5	April	900	2010

For example, the cell C3 is a sum of C2+B3. This would be very simple formula in excel but how can i achieve this new measure in PowerBI?

[v-lid-msft]

Hi @Anonymous ,

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

Best regards,

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

We can try to use the following measure to meet your requirement:

New Measure =
SUMX (
    SUMMARIZE (
        FILTER (
            ALLSELECTED ( 'Calendar' ),
            'Date'
                <= CALCULATE (
                    MAX ( 'Calendar'[Date] )
                )
        ),
        'Calendar'[Date].[Year],
        'Calendar'[Date].[Month],
        "Measure Value", CALCULATE (
            [Measure]
        )
    ),
    [Measure Value]
)

If it doesn't meet your requirement, could share your sample data based on the expected you have shared to me if you don't have any Confidential Information? Please upload your files to One Drive and share the link here.

Best regards,

[amitchandak]

Make sure you have a date table and joined with date of your table and try like

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,date[date])))

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,endofmonth(dateadd(date[date]),-1,month))))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/