cancel
Showing results for
Did you mean:
Highlighted Helper II

## Convert Excel Formula to DAX

Hello,

I have this table in Excel It contains

1. Date

2. Col1 is a fixed column

3. Col3 is a fixed column

I want to compute Col2 and Col4 Starting with Col2

1. Col2 Starts with same value in B2 (because nothing is in previous values)

2. Then in Month 2 , C3 = B3+C2 - D3 = 4

I know how to access previous value in DAX, but wont help much

``````CALCULATE(
sum(Table[Col1]),
PREVIOUSMONTH(Table[Date]),
)``````

Issue Persists in the steps below( in Month3) , now I want to use values that were computed in step above How can I do it in DAX for the highlighted cells ?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted Community Support

## Re: Convert Excel Formula to DAX

Hi @myou,

It seems like a simple rolling total of [B] -[D] fields with condition that less than or equal to the current row. You can create calculated columns with below formulas to achieve your requirement:

``````C =
SUMX (
FILTER ( 'Table', [Date] <= EARLIER ( 'Table'[Date] ) ),
[B] - [D]
)

E = Table[C]-Table[D]``````

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Highlighted Super User IV

## Re: Convert Excel Formula to DAX

@myou , prefer to use a date calendar and take month from that

Cumm =
CALCULATE(SUM(Table[B]),filter(date,date[date] <=max(Table[Date]))) // Or the first value of B
+ CALCULATE(SUM(Table[C]),filter(date,date[date] <=max(Table[Date])))
-CALCULATE(SUM(Table[D]),filter(date,date[date] <=max(Table[Date])))

All are cumulative totals. The one from B can the first value

My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted Helper II

## Re: Convert Excel Formula to DAX

Why Table[C] is repeated twice ?

Highlighted Super User IV

## Re: Convert Excel Formula to DAX

@myou , updated the post. It needs be column D

My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted Community Support

## Re: Convert Excel Formula to DAX

Hi @myou,

It seems like a simple rolling total of [B] -[D] fields with condition that less than or equal to the current row. You can create calculated columns with below formulas to achieve your requirement:

``````C =
SUMX (
FILTER ( 'Table', [Date] <= EARLIER ( 'Table'[Date] ) ),
[B] - [D]
)

E = Table[C]-Table[D]``````

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

Announcements #### Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members. #### Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start. #### Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries! Top Solution Authors
Top Kudoed Authors
Users online (711)