topic Re: Dax Running total after certain Month in DAX Commands and Tips

Dax Running total after certain Month

Anonymous — Thu, 28 May 2020 20:51:55 GMT

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! 🙂

Re: Dax Running total after certain Month

speedramps — Thu, 28 May 2020 21:45:22 GMT

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)

Re: Dax Running total after certain Month

Anonymous — Fri, 29 May 2020 08:47:26 GMT

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

Re: Dax Running total after certain Month

speedramps — Fri, 29 May 2020 09:47:18 GMT

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)

Re: Dax Running total after certain Month

Anonymous — Fri, 29 May 2020 10:25:40 GMT

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)

Re: Dax Running total after certain Month

speedramps — Fri, 29 May 2020 11:00:27 GMT

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)

Re: Dax Running total after certain Month

speedramps — Fri, 29 May 2020 11:13:05 GMT

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.

Re: Dax Running total after certain Month

Anonymous — Fri, 29 May 2020 11:30:11 GMT

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?

Re: Dax Running total after certain Month

Anonymous — Fri, 29 May 2020 11:33:08 GMT

Yes I do have filters applied to the matrix!

Product is not Shoes/Socks/jumpers..

Re: Dax Running total after certain Month

Anonymous — Fri, 29 May 2020 11:40:36 GMT

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?

Re: Dax Running total after certain Month

Anonymous — Fri, 29 May 2020 14:25:27 GMT

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)

Re: Dax Running total after certain Month

speedramps — Fri, 29 May 2020 15:45:40 GMT

Well done.