Running Total after certain Month

[Anonymous]

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

Product	jan	feb	mar	apr	may	jun	jul
Trousers (Current)	2000	4000	3000	500	600	800	100
Trousers (Desired)	2000	4000	3000	3500	4100	4900	5000

thank you in advance!

[V-lianl-msft · Accepted solution]

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.

[amitchandak]

@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]

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?

Product	Jan	Feb	Mar	Apr
Trousers (raw data)	2000	1000	500	600
Trousers (desired)	2000	1000	1500	2100
Tshirts (Desired)	3000	4000	4600	5200

[amitchandak]

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