Running Total from Current Month Onwards

[Jiro]

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!

[v-yilong-msft]

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

[Dangar332]

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 ?

[123abc]

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.