Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Running Total after certain Month

Hi Experts,

Having a little difficulty in getting Power BI to calculate a running total after a certain month.

 

So I am trying to calculate the running total after a certain month. But its a little complicated. The months after March are forecasted and need to be running totals from the previous month. So April should be 3500 for Trousers from the example below. However in the raw data it is actually 500. Is there a way to manipulate the data like this without affecting the prior months?

 

I have 2 tables in the model

Sales table

Calender table

(The product field is in the Sales table)

-Sales Field

-Date Field

-Product Field

 

I am using a matrix to visualise this...

 

Productjanfebmaraprmayjunjul
Trousers (Current)200040003000500600800100

Trousers (Desired)

2000400030003500410049005000

 

thank you in advance!

Data Protection
  • V-lianl-msft's avatar
    V-lianl-msft
    6 years ago

    Hi Anonymous ,

     

    Based on what you have described, you want to calculate the cumulative total after a month. Is a certain month a fixed value? Or a dynamic value? If it's a dynamic value, what's the logic behind it.

    If the month is a fixed value,please try to create measure like this.

    RunningTotal =
IF (
    MAX ( 'Table'[date] ) >= DATE ( 2020, 3, 1 ),
    CALCULATE (
        SUM ( 'Table'[sales] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[product] ),
            'Table'[date] >= DATE ( 2020, 3, 1 )
                && SUMX (
                    FILTER ( 'Table', EARLIER ( 'Table'[date] ) <= 'Table'[date] ),
                    'Table'[sales]
                )
        )
    ),
    MAX ( 'Table'[sales] )
)

    Sample .pbix

     

    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous ,

    Try like

    Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,date[date])), Date[Date]>=date(2020,01,31))

    or

    Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=max(Sales[Sales Date])), Date[Date]>=date(2020,01,31))

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi amitchandak 

       

      thank you for your suggestion. But it is still not working. Tried both your formulas..

      Does this calc work on Product line level?

       

      Is it possible to only use the date in the Sales table?

       

      ProductJanFebMarApr
      Trousers (raw data)20001000500600
      Trousers (desired)2000100015002100
      Tshirts (Desired)3000400046005200
      • amitchandak's avatar
        amitchandak
        Super User
        6 years ago

        Anonymous , Can you share raw data and sample output in table format? Or a sample pbix after removing sensitive data.

  • V-lianl-msft's avatar
    V-lianl-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    Based on what you have described, you want to calculate the cumulative total after a month. Is a certain month a fixed value? Or a dynamic value? If it's a dynamic value, what's the logic behind it.

    If the month is a fixed value,please try to create measure like this.

    RunningTotal =
IF (
    MAX ( 'Table'[date] ) >= DATE ( 2020, 3, 1 ),
    CALCULATE (
        SUM ( 'Table'[sales] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[product] ),
            'Table'[date] >= DATE ( 2020, 3, 1 )
                && SUMX (
                    FILTER ( 'Table', EARLIER ( 'Table'[date] ) <= 'Table'[date] ),
                    'Table'[sales]
                )
        )
    ),
    MAX ( 'Table'[sales] )
)

    Sample .pbix

     

    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.