Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
AishwariyaV
Helper IV
Helper IV

Cumulative to non cumulative

Capture.PNG

Capture3.PNG

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
Mariusz
Community Champion
Community Champion

Hi @AishwariyaV 

 

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.
Mariusz Repczynski

 

View solution in original post

HI @AishwariyaV 

 

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.
Mariusz Repczynski

 

View solution in original post

6 REPLIES 6
Mariusz
Community Champion
Community Champion

Hi @AishwariyaV 

 

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.
Mariusz Repczynski

 

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.

HI @AishwariyaV 

 

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.
Mariusz Repczynski

 

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 ?

Hi @AishwariyaV 

 

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.
Mariusz Repczynski

 

amitchandak
Super User
Super User

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.