Forum Discussion

Jiro's avatar
Jiro
Frequent Visitor
2 years ago

Running Total from Current Month Onwards

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!

Need help!

3 Replies

  • 123abc's avatar
    123abc
    Icon for Community Champion rankCommunity Champion
    2 years ago

    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.

  • Dangar332's avatar
    Dangar332
    Icon for Resident Rockstar rankResident Rockstar
    2 years ago

    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 ?

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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