Forum Discussion

JurriaanQEL's avatar
JurriaanQEL
Regular Visitor
8 years ago
Solved

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 ...
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    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




     

     

     

     

     

JurriaanQEL's avatar
JurriaanQEL
Regular Visitor
8 years ago

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

  • JurriaanQEL's avatar
    JurriaanQEL
    Regular Visitor
    8 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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.