Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi Everyone,
I am trying to compute running total of outbound quantity with additional conditions:
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!
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
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 ?
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
21 | |
21 | |
19 | |
13 | |
12 |
User | Count |
---|---|
42 | |
28 | |
23 | |
22 | |
22 |