Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculate cumulative return

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.

 

 ABC
1 MeasureNew Measure
2January500500
3February10510
4March6001110
5April9002010
    

 

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?

Need Help
Tutorial Requests
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    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,

     

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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/

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    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,

     

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    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,