topic Re: Running Total from Current Month Onwards in DAX Commands and Tips

Running Total from Current Month Onwards

Jiro — Tue, 05 Mar 2024 05:36:10 GMT

Hi Everyone,

I am trying to compute running total of outbound quantity with additional conditions:

Cumulative quantity starting from the first date of current month up to any dates in the future
Filter context on date is expected to be from March 1st up to June 30th, 2024.

I have tested with 2 measures below and they still haven't met the business requirements.

The former here is adding up cumulative quantity way back to December 2022, and this is indeed not what we are after:

Outbound RT = VAR MaxDate = MAX ( 'DATE'[Date] ) RETURN CALCULATE ( [Outbound], 'DATE'[Date] <= MaxDate, ALL ( 'DATE' ) )

Whereas, this is starting to add up cumulative quantity from "Today" onwards, meaning that any outbound before current date are not factored in as part of calculation:

Outbound RT 2 = VAR MaxDate = MAX ( 'DATE'[Date] ) RETURN CALCULATE ( [Outbound], 'DATE'[Date] <= MaxDate && 'DATE'[Date] >= TODAY(), ALL ( 'DATE' ) )

Expected Results:

DAX code that starts computing from the first day of current month (March in this stance) onwards to any future dates. The code should add up cumulative quantity starting from March 1st, 2024.

Is there anyone who can guide me through? Thank you so much!

Re: Running Total from Current Month Onwards

123abc — Tue, 05 Mar 2024 05:55:15 GMT

To achieve the desired outcome of computing the running total of outbound quantity starting from the first day of the current month onwards, you can use the following DAX code:

Outbound RT =
VAR CurrentMonthStartDate = DATE(YEAR(TODAY()), MONTH(TODAY()), 1)
VAR MaxDate = MAX('DATE'[Date])
RETURN
CALCULATE (
[Outbound],
'DATE'[Date] >= CurrentMonthStartDate,
'DATE'[Date] <= MaxDate,
ALL('DATE')
)

This DAX code calculates the running total of the outbound quantity, starting from the first day of the current month (March 1st, 2024) up to any future dates within the filter context of March 1st to June 30th, 2024.

Ensure that you have a 'DATE' table with a column named 'Date' that contains all the dates you want to consider in your analysis. Replace [Outbound] with the appropriate measure or column name representing your outbound quantity.

This code should give you the desired cumulative quantity starting from March 1st, 2024, to any future dates within the specified filter context.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

Re: Running Total from Current Month Onwards

Dangar332 — Tue, 05 Mar 2024 06:04:25 GMT

hi, Jiro

try below code

Outbound RT 2 = VAR MaxDate = MAX ( 'DATE'[Date] ) var month_startdate= EOMONTH(today(),-1)+1 RETURN CALCULATE ( [Outbound], 'DATE'[Date] <= MaxDate && 'DATE'[Date] >= month_startdate, ALL ( 'DATE' ) )

and i have question
why you use ALL('DATE') INSIDE CALCULATE ?

Re: Running Total from Current Month Onwards

Anonymous — Wed, 03 Apr 2024 09:26:05 GMT

Hi Jiro ,

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered to close this topic?

Best Regards

Yilong Zhou