Dax Running total after certain Month

[Anonymous]

Hi Guys,

Please could you help with a calculation i need. 

I want to calculate Row B. Row B turns into a rolling total after March

	Jan	Feb	Mar	Apr	May
A	15	8	40	2	4
B	15	8	40	42	46

Row A is Sum(Sales)

Is there a Dax formula that will allow me to do this? 

Would be helpful to have a hard code formula. And a dynamic formula which calcs a running total after the current month 

Thank you in advance! 🙂

[speedramps · Accepted solution]

Hi aaron

Please consider this solution and leave kudos ..

Cummulative total =

VAR mydynamicdate = DATEVALUE("1/3/2020")  -- set the start date dynamically

VAR mymaxdate = MAX(Sales[Date])                   -- this calcs the max date each month to improves performance 

VAR totalqty=SUM(Sales[Qty])                          

VAR cummulativeqty=                                           

CALCULATE(SUM(Sales[Qty]),

FILTER(ALL(Sales),

Sales[Date]>=mydynamicdate &&

Sales[Date]<=mymaxdate))

RETURN

If(mymaxdate<mydynamicdate,

totalqty,

cummulativeqty)

[Anonymous]

Hi Unfortunately it didnt work....

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?

-Sales Field

-Date Field

-Product Field

I do have a date field and is linked to a calendar. I have 2 tables one is sales and other is calendar. Product field is in Sales

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

[speedramps]

Hi again aaron

The solution I provided seems to display the results exactly as you desire.

Click here to download an example 

Cummulative total =

VAR mydynamicdate = DATEVALUE("1/3/2020") -- set the start date dynamically

VAR mymaxdate = MAX(Sales[Date]) -- this calcs the max date each month to improves performance

VAR totalqty=SUM(Sales[Qty])

VAR cummulativeqty=

CALCULATE(SUM(Sales[Qty]),

FILTER(ALL(Sales),

Sales[Date]>=mydynamicdate &&

Sales[Date]<=mymaxdate))

RETURN

If(mymaxdate<mydynamicdate,

totalqty,

cummulativeqty)

[Anonymous]

Hi @speedramps 

Yes your example is correct!

But it is not working on mine...

After March it is doing this...

Below is the Dax used:

Cummulative total = 
VAR mydynamicdate = DATEVALUE("1/4/2020")  -- set the start date dynamically
VAR mymaxdate = MAX('Cap'[Month Year])                   -- this calcs the max date each month to improves performance 
VAR totalqty=SUM('Cap'[Value])                          
VAR cummulativeqty=                                           
CALCULATE(SUM('Cap'[Value]),
FILTER(ALL('Cap'),
'Cap'[Month Year]>=mydynamicdate &&
'Cap'[Month Year]<=mymaxdate))

RETURN
If(mymaxdate<mydynamicdate,
totalqty,
cummulativeqty)

[speedramps]

Hi arron

It wont work because you changed the logic from date to month year
VAR mymaxdate = MAX('Cap'[Month Year]) 

'Cap'[Month Year]>=mydynamicdate &&
'Cap'[Month Year]<=mymaxdate))

You need to to use your Date field and then use the month in the visual.
The Cap'[Date]<=mymaxdate logic will then calualte the month end dates automatically.

see my example

Download this example 

Cummulative total =
VAR mydynamicdate = DATEVALUE("1/4/2020") -- set the start date dynamically
VAR mymaxdate = MAX('Cap'[Date) -- this calcs the max date each month to improves performance
VAR totalqty=SUM('Cap'[Value])
VAR cummulativeqty=
CALCULATE(SUM('Cap'[Value]),
FILTER(ALL('Cap'),
'Cap'[Date]>=mydynamicdate &&
'Cap'[Date]<=mymaxdate))

RETURN
If(mymaxdate<mydynamicdate,
totalqty,
cummulativeqty)

[Anonymous]

Figured it out....

But thank you.

Cummulative total =
VAR mydynamicdate = DATEVALUE("1/4/2020") -- set the start date dynamically
VAR mymaxdate = MAX('Cap'[Date) -- this calcs the max date each month to improves performance
VAR totalqty=SUM('Cap'[Value])
VAR cummulativeqty=
CALCULATE(SUM('Cap'[Value]),
FILTER(ALLEXCEPT('Cap','Cap',[Product]),
'Cap'[Date]>=mydynamicdate &&
'Cap'[Date]<=mymaxdate))

RETURN
If(mymaxdate<mydynamicdate,
totalqty,
cummulativeqty)

[speedramps]

Well done.

[Anonymous]

Hi @speedramps 

It is still not working..... Please could you advise. I have done exactly how you have said but it is still not working for me...

I have tried using date instead but still nothing..

what information can i give you to better analyze? 

[speedramps]

Also note the filter contains an ALL command

FILTER(ALL('Cap'),
'Cap'[Date]>=mydynamicdate &&
'Cap'[Date]<=mymaxdate))

So if you are using slicers or other filters the ALL will remove them. Which might explain your problem.

But you can easily reinstate these with within the CALCULATE FILTER command.

It is tricky for me to help without seeing the PBIX.

[Anonymous]

Hi @speedramps 

I have now removed the filters from Power Query. I am getting the correct totals. But for every product it is duplicating the total after March.

Could you advise?

[Anonymous]

Yes I do have filters applied to the matrix! 

Product is not Shoes/Socks/jumpers..