How to calculate Rolling Cost?

[yodha]

Hi,

am very new to power bi.

i have Matrix in my .pbix like this,

and i want to calculate rolling cost, and final result should be like this,

Any help on this is much appreciated.

Thanks in Advance.

[VijayP · Accepted solution]

@yodha 

You can use this function to get the cumulative.

I am sharing the PBIX which you can download form here

If you find this as solution please mentione this as solution and share your Kudos.

Also you can watch my Videos on www.youtube.com/perepavijay

Regards

Vijay Perepa

[v-yingjl · Accepted solution]

Hi @yodha ,

You can follow these steps to try:

1. I have created a calculated column to fomat date column for easy calculation:

Month-Year = VALUE(FORMAT('Table'[Date],"yyyymm"))​

2. I have created a measure to calculate present cost to replace previous column because I thought it was contradictory when calculating total cost basd on previous month:

Present cost measure =
VAR _minmonthyear =
    CALCULATE ( MIN ( 'Table'[Month-Year] ), ALLSELECTED ( 'Table' ) )
VAR _cmonthyear =
    SELECTEDVALUE ( 'Table'[Month-Year] )
VAR _lastmonthyear =
    CALCULATE (
        MAX ( 'Table'[Month-Year] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Month-Year] < _cmonthyear )
    )
RETURN
    IF (
        _cmonthyear = _minmonthyear,
        SUM ( 'Table'[Present cost] ),
        CALCULATE (
            SUM ( 'Table'[Present cost] ),
            FILTER (
                ALLEXCEPT ( 'Table', 'Table'[Categoary] ),
                'Table'[Month-Year] = _minmonthyear
            )
        )
            - CALCULATE (
                SUM ( 'Table'[Forecast cost] ),
                FILTER (
                    ALLEXCEPT ( 'Table', 'Table'[Categoary] ),
                    'Table'[Month-Year] < _cmonthyear
                )
            )
    )
​

3. Finally, create a measure to calculate total cost:

Total cost = [Present cost measure]-SUM('Table'[Forecast cost])​

4. You will get the result like this:

Here is my sample file that hopes to help you, please try it: PBIX 

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

https://community.powerbi.com/t5/Quick-Measures-Gallery/Rolling-Months/m-p/391499#M124

https://community.powerbi.com/t5/Quick-Measures-Gallery/Rolling-Weeks/m-p/391694#M128

[VijayP · Accepted solution]

@yodha 

You can use this function to get the cumulative.

I am sharing the PBIX which you can download form here

If you find this as solution please mentione this as solution and share your Kudos.

Also you can watch my Videos on www.youtube.com/perepavijay

Regards

Vijay Perepa