cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JurriaanQEL
Regular Visitor

Remove cumulative total

Hi, 

 

Every month I receive a file with updated numbers.

One of the columns is cumulative, e.g.:

January     -    20

February   -    25

March       -    40

 

I want to have a table in Power BI saying that the difference between February and January is '5' (which is the actual value) and the difference between March and February is 15.

I've found below topic, I want to do exactly the opposite of it... Still a newby at this...

http://community.powerbi.com/t5/Desktop/Cumulative-Amounts/m-p/54011

 

Thanks.

1 ACCEPTED SOLUTION

Hi @JurriaanQEL

 

Please see the attached file here.

Hope this helps. Here are the steps I performed

 

As you said we need an assistant field to undo the Cumulatives. So
First I created a "Parameter Table" to get "month number" in our Main Table

Then we can use this Calculated Column to get Monthly figures

Monthly Figure =
VAR PreviousMonthValue =
    CALCULATE (
        SUM ( Table1[Cumulative] ),
        FILTER (
            Table1,
            Table1[Year] = EARLIER ( Table1[Year] )
                && Table1[MonthNumber]
                    = EARLIER ( Table1[MonthNumber] ) - 1
        )
    )
RETURN
    Table1[Cumulative] - PreviousMonthValue




 

 

 

 

 

Regards
Zubair

Please try my custom visuals


View solution in original post

14 REPLIES 14
Zubair_Muhammad
Community Champion
Community Champion

Hi@ @JurriaanQEL

 

Could you please copy paste sample data just like in the post you referred to?

Do you have other columns beside month and amount?

Regards
Zubair

Please try my custom visuals


Hi @Zubair_Muhammad Thanks for your quick reply.

Below the data table that comes from Power BI.

The source data is not one table. Every month I receive a file, which is added to the source data. In this data there is a field called in This Month. You can see that for example March 2016, this value was: 3080, where in April 2016 this value was 35755. The difference between the 2 (32675) is the non cumulated number for April. Somehow I want to be able to see this figure somewhere (I've tried it with work arounds, but I don't have any other source fields (date) that can help me here). 

 

 

Capture.JPG

Hi @JurriaanQEL

 

Please see the attached file here.

Hope this helps. Here are the steps I performed

 

As you said we need an assistant field to undo the Cumulatives. So
First I created a "Parameter Table" to get "month number" in our Main Table

Then we can use this Calculated Column to get Monthly figures

Monthly Figure =
VAR PreviousMonthValue =
    CALCULATE (
        SUM ( Table1[Cumulative] ),
        FILTER (
            Table1,
            Table1[Year] = EARLIER ( Table1[Year] )
                && Table1[MonthNumber]
                    = EARLIER ( Table1[MonthNumber] ) - 1
        )
    )
RETURN
    Table1[Cumulative] - PreviousMonthValue




 

 

 

 

 

Regards
Zubair

Please try my custom visuals


View solution in original post

Hi again @Zubair_Muhammad

 

After a frustrating few tries, I have to admit defeat 😞

I didn't manage to make it work in my workbook.

I have the fields Month, Year and Monthname already in my dim_date table, so I didn't need to create a separate table for this (I think?). 

So I changed your DAX to fit my workbook, which you can see below.

Your cumulative field is called (CA) Indemnity Paid (This Month) for me. 

 

I first had v_f_Bordereau_line[(CA) Indemnity Paid (This Month)] in the DAX, but that gave me this error:

A single value for column '(CA) Indemnity Paid (This Month)' in table 'v_f_Bordereau_line' cannot be determined.

Then I changed it to the sum(v_f_Bordereau_line[(CA) Indemnity Paid (This Month)]), which results in what you can see below.

You can see in the second table (on the right) that the total is 20,032,538.51, but that from March 2016 this is gradually growing (and is the cumulative part). Why I don't get the same in the left table is beyond me. When removing the Sum (Don't Summarize) it will give me the error 'Can't determine relationships between the fields'... so yeah, I am very lost!

Any thoughts??

Capture2.JPG

 

 

Thanks in advance!

Kind regards, Jurriaan

Hi Jurriaan
Could you upload your file to one drive or googledrive
And share link here

Regards
Zubair

Please try my custom visuals


Hi Zubair,

 

Thanks for the quick reply.

I've uploaded it, after stripping out some fields, data, etc (business information....)

https://1drv.ms/u/s!AiecK9GUkh9Aqyx8cBitsE4kZgW4

I hope I didn't remove too much.. I've left the fields I think should be there.

 

Thanks again.

 

Kind regards,

Jurriaan

 

Ps. I took a look at your website... nice stuff!

Hi
I will get my hands on laptop in 2 to 3 hours
Replying from my phone

I will do my best
Regards
Zubair

Please try my custom visuals


No rush, I am calling it a day soon anyway and I am more than grateful for you giving it a try anyway!

Hi @JurriaanQEL

 

In the file you sent, there is no relationship between the dim_date and v_f_Bordereau tables.

 

When I create a relationship,the left table shows both Cumulative and Monthly Figures.

 

Right table has missing fields (probably because you deleted some data). So it is not showing up in the File You sent

Regards
Zubair

Please try my custom visuals


Hi @Zubair_Muhammad

 

Ok, so I am close... 

May I ask you to share the file where you managed it with the relationships? Because even with that I am struggling (can't autodetect, or can't create relationship because one of the columns must have unique values)... I think I need to do a course in Power BI...

It's correct that the right table isn't working, I removed one field too many, but since that one was just to show you what numbers there are it wasn't very relevant anyway (and it was showing in the picture).

 

Thanks as always, very much appreciated.

 

Kind regards,

Jurriaan

Hi @JurriaanQEL

 

Here it goes

 

https://1drv.ms/u/s!AhiuKaBxxAufjlj_7cIa7Xllk9u4

 

See the First item with No Month YEAR but a figure for CA Indemnity Paid.

I believe it means your Dim_Date doesot contain all the necessary dates.

 

These books are precious for learning DAX

 

Learn to Write DAX: A practical guide to learning Power Pivot for Excel and Power BI

Definitive Guide To DAX

Regards
Zubair

Please try my custom visuals


Anonymous
Not applicable

Hi @Zubair_Muhammad,

Its works for me when there is only date in the table, but the moment I add third column, it gives me the wrong result.
For instance, I have date, country and CumulativeSales and I need daily sales. 


Please can you assist on this one as well.

 

I have modified the data in your pbix file itself. Please check this here. 

Hi again @Zubair_Muhammad,

 

I thinky you might be on to something.

There is indeed another column that has a date field. 

I've uploaded the less-stripped version:

https://1drv.ms/u/s!AiecK9GUkh9Aqy7ukkq6ENOQ288c

The date field consists of year,quarter,month and day. How would that hold up in the DAX?

 

Cheers

Thanks a lot! Gonna work on this Man Happy

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!