cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper IV

## Cumulative to non cumulative

In this example table we have cumulative data for a specific cc no. and month number. Now I would want to find the actual revenue in power bi itself. For reference I have put the actual rev. value in the table

2 ACCEPTED SOLUTIONS
Community Champion

Try the below.

```Column =
VAR _month = 'Table'[Month No]
VAR _previousRev =
CALCULATE(
MAX( 'Table'[cumulative rev.] ),
ALLEXCEPT( 'Table', 'Table'[cc no.] ),
'Table'[Month No] < _month
)
RETURN 'Table'[cumulative rev.] - _previousRev```

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

Community Champion

Sure.

```Column =
VAR _month = 'Table'[Month No] --varieble captures Month no in current row
VAR _previousRev = -- varieble taht calculates previous revenue
CALCULATE(
MAX( 'Table'[cumulative rev.] ), --3 Max Revenue amount for months lower then current month
ALLEXCEPT( 'Table', 'Table'[cc no.] ), --1 ALLEXCEPT removes filter context from 'Table' apart from one on Table'[cc no.]
'Table'[Month No] < _month --2 filters table further to include months lower the one in the current row
)
RETURN 'Table'[cumulative rev.] - _previousRev```

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

6 REPLIES 6
Community Champion

Try the below.

```Column =
VAR _month = 'Table'[Month No]
VAR _previousRev =
CALCULATE(
MAX( 'Table'[cumulative rev.] ),
ALLEXCEPT( 'Table', 'Table'[cc no.] ),
'Table'[Month No] < _month
)
RETURN 'Table'[cumulative rev.] - _previousRev```

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

Helper IV

Hey @Mariusz ,

Thaks fror the reply.It has solved my problem but Iam not able to understand the logic behind the query. Can  you give a brief explanation of the logic so that I can extend it to my fairly big actual data set in a more informed manner.

Community Champion

Sure.

```Column =
VAR _month = 'Table'[Month No] --varieble captures Month no in current row
VAR _previousRev = -- varieble taht calculates previous revenue
CALCULATE(
MAX( 'Table'[cumulative rev.] ), --3 Max Revenue amount for months lower then current month
ALLEXCEPT( 'Table', 'Table'[cc no.] ), --1 ALLEXCEPT removes filter context from 'Table' apart from one on Table'[cc no.]
'Table'[Month No] < _month --2 filters table further to include months lower the one in the current row
)
RETURN 'Table'[cumulative rev.] - _previousRev```

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

Helper IV

Hey @Mariusz ,

The DAX logic works just fine with my data-set but then I should Ideally do a lot of work at Edit Queries level like

un-pivoting,transposing etc. for that I should apply the same logic at the edit queries level. Can you help out with this prob. in a way ?

Community Champion

Please see the attached file with three different solutions.

1. DAX column in "Table"
2. M ( Query Editor )  in "Table"
3. UI ( Query Editor )  in "Table A/B"

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

Super User

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors