How to find the running diffrence of two columns using dax

[adityakumar619]

Hi Team,

Can you please help to find the Running diff total of below two columns i.e DEPR_Value and Total cost

Note:here Total cost is the fixed value

first row o/p :16680-170.45=16509.55

second row o/p:16509.55-170.45=16339.1

third row o/p:16339.1-0.63=16338.47

and so on.

Also i tried with different Dax but no luck.

SERIAL	FISCAL_YEAR	PERIOD	DEPR_Value	Total cost	Running diff total
MXQ9140	2019	4	170.45	16680	16509.55
MXQ9140	2019	5	170.45	16680	16339.1
MXQ9140	2019	5	0.63	16680	16338.47
MXQ9140	2019	5	0.63	16680	16337.84
MXQ9140	2019	6	213.06	16680	16124.78
MXQ9140	2019	6	0.78	16680	16124
MXQ9140	2019	7	170.45	16680	15953.55
MXQ9140	2019	7	0.63	16680	15952.92
MXQ9140	2019	8	170.45	16680	15782.47

[Anonymous]

Hi @adityakumar619 ,

Please try below steps:

1. please add a new index column in Power Query Pane

2. create a measure with below dax formula

Running diff total =
VAR total_cost =
    SELECTEDVALUE ( 'Table'[Total cost] )
VAR cur_index =
    SELECTEDVALUE ( 'Table'[Index] )
VAR tmp =
    FILTER ( ALL ( 'Table' ), [Index] <= cur_index )
VAR _a =
    SUMX ( tmp, [DEPR_Value] )
RETURN
    total_cost - _a

3. add a table visual with fields and measure

Please refer the attached .pbix file

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[adityakumar619]

@Anonymous I hope you got my query,Please let me know if you need any further details.

[Anonymous]

Hi @adityakumar619 ,

Sorry, I could not access the link, please share the desensitized .pbix file with other way.

It would be good to provide a screenshot of the results you are expecting and desensitized example data.

Best regards,
Community Support Team_Binbin Yu

[adityakumar619]

@Anonymous for sure will accept it,but just to clarify what if i have multiple serial numbers.can you please check once on this.

[adityakumar619]

https://drive.google.com/file/d/18oglhVf_NCrPrjanuQ-0Uf6SklhVVg-Z/view?usp=sharing 

@Anonymous  I have attached the pbix file in my one drive ,Please find the above link(I have given the permission to access the file aswell) i don't have any option to upload the files directly.

Sorry for that.In this pbix file i have all the details interms of what is expected.

Excel Data:

https://docs.google.com/spreadsheets/d/1c3INBU7GMcW-dwtE8jVpupyx90dx-WVv/edit?usp=sharing&ouid=107317045258698142200&rtpof=true&sd=true 

[Anonymous · Accepted solution]

Hi @adityakumar619 ,

Please try to create a new column with below dax formula:

Column =
VAR cur_serial = [SERIAL]
VAR cur_date = [Date]
VAR tmp =
    FILTER ( ALL ( 'Table' ), [SERIAL] = cur_serial && [Date] <= cur_date )
VAR min_date =
    MINX ( tmp, [Date] )
VAR start_val =
    CALCULATE (
        MAX ( [Total cost] ),
        'Table'[SERIAL] = cur_serial,
        'Table'[Date] = min_date,
        ALL ( 'Table' )
    )
VAR sum_dv =
    SUMX ( tmp, [DEPR_Value] )
RETURN
    start_val - sum_dv

Please refer the attached .pbix file

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[adityakumar619]

@Anonymous I hope you access the above content,if not will share via your mail id.

[adityakumar619]

https://drive.google.com/file/d/1ljuyWkmQT5E4bp8gowcGWaC4gfBXAa-y/view?usp=drive_link 

[adityakumar619]

@Anonymous I have added the pbix file in google drive and i also added multiple serial numbers previously if you see only one serial number 

[adityakumar619]

SERIAL	FISCAL_YEAR	PERIOD	DEPR_Value	Total cost	Running diff total
MXQ9140	2019	4	170.45	16680	16509.55
MXQ9140	2019	5	170.45	16680	16339.1
MXQ9140	2019	5	0.63	16680	16338.47
MXQ9140	2019	5	0.63	16680	16337.84
MXQ9140	2019	6	213.06	16680	16124.78
MXQ9140	2019	6	0.78	16680	16124
MXQ9140	2019	7	170.45	16680	15953.55
MXQ9140	2019	7	0.63	16680	15952.92
MXQ9140	2019	8	170.45	16680	15782.47

Sample data

[adityakumar619]

Can someone please help on the Dax to accomplish the desired calculation