"Change cumulative to periodic values

[MrDarian]

"Change cumulative to periodic values

Hello

in power bi  I have a table that is cumuative, so i want to write a measure to change it to periodical value. i want to have last column ( Yellow Column)"

@amitchandak

[v-alq-msft · Accepted solution]

Hi, @MrDarian 

Based on your description, I created data to reproduce your scenario. The sample pbix file is attachded in the end.

Table:

You may create a calculated column or a measure as below.

Periodic Amount Column = 
var _lastamount = 
CALCULATE(
    SUM('Table'[Cumnlative Amount]),
    FILTER(
        ALL('Table'),
        'Table'[Title ID] = EARLIER('Table'[Title ID])&&
        'Table'[Month ID] = EARLIER('Table'[Month ID])&&
        'Table'[Week ID] = EARLIER('Table'[Week ID])-1
    )
)
return
IF(
    ISBLANK(_lastamount),
    'Table'[Cumnlative Amount],
    'Table'[Cumnlative Amount]-_lastamount
)

Periodic Amount Measure = 
var _lastamount = 
CALCULATE(
    SUM('Table'[Cumnlative Amount]),
    FILTER(
        ALL('Table'),
        'Table'[Title ID] = SELECTEDVALUE('Table'[Title ID])&&
        'Table'[Month ID] = SELECTEDVALUE('Table'[Month ID])&&
        'Table'[Week ID] = SELECTEDVALUE('Table'[Week ID])-1
    )
)
return
IF(
    ISBLANK(_lastamount),
    SELECTEDVALUE('Table'[Cumnlative Amount]),
    SELECTEDVALUE('Table'[Cumnlative Amount])-_lastamount
)

Result:

Best Regards

Allan

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

[MrDarian]

hello, thanks for your response, is it possible to do in my real data in the link below?

https://gofile.io/d/YmMgs2 

[amitchandak]

@MrDarian , Create a new column as

period Amount =		
table[cumulative Amount]  - maxx(filter(table, table[title ID] = earlier(table[title ID]) 	&& table[cumulative Amount] < earlier(table[cumulative Amount] )),table[cumulative Amount] )

[MrDarian]

this is my power bi file link download

https://gofile.io/d/YmMgs2 

[MrDarian]

it does'nt work

My DAX Formula:

period Amount =
'Weekly Performance'[SPerformanceAmount]
    - MAXX (
        FILTER (
            'Weekly Performance',
            'Weekly Performance'[SRefActionId]
                = EARLIER ( 'Weekly Performance'[SRefActionId] )
                && 'Weekly Performance'[SPerformanceAmount]
                    < EARLIER ( 'Weekly Performance'[SPerformanceAmount] )
        ),
        'Weekly Performance'[SPerformanceAmount]
    )
    
    

[parry2k]

@MrDarian or use following DAX expression to create a Column, change Table and Column names as per your model:

Periodic Value = 
VAR __currValue = **bleep**[Column1]
VAR __prevValue = 
    CALCULATE ( 
        MAX ( **bleep**[Column1] ), 
        ALLEXCEPT ( **bleep**, **bleep**[Id] ), 
        **bleep**[Column1] < __currValue 
    ) 
RETURN __currValue - __prevValue

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[MrDarian]

It does'nt work

My DAX for Column:

Periodic Value =

VAR __currValue = 'Weekly Performance'[SPerformanceAmount]

VAR __prevValue =

CALCULATE (

MAX ('Weekly Performance'[SPerformanceAmount] ),

ALLEXCEPT ( 'Weekly Performance', 'Weekly Performance'[Id] ),

'Weekly Performance'[SPerformanceAmount] < __currValue

)

RETURN __currValue - __prevValue

[parry2k]

@MrDarian please share pbix file with sample data to further look into it. Remove any sensitive information before sharing.

[MrDarian]

https://gofile.io/d/YmMgs2