RT_QTY V2 =
VAR MaxDate =
MAX ( fct_ReqTrans[REQUIREMENTDATE] )
VAR DatesToUse =
FILTER (
ALL ( fct_ReqTrans[REQUIREMENTDATE] ),
fct_ReqTrans[REQUIREMENTDATE] <= MaxDate
)
VAR CurrentREFID =
SELECTEDVALUE ( fct_ReqTrans[REFID] )
VAR ProductsOfTheCurrentDate =
CALCULATETABLE (
VALUES ( fct_ReqTrans[REFID] ),
REMOVEFILTERS ( fct_ReqTrans[REFID] )
)
VAR ProductsOfTheCurrentDateAndRank =
ADDCOLUMNS (
ProductsOfTheCurrentDate,
"@CR", RANKX ( ProductsOfTheCurrentDate, fct_ReqTrans[REFID],, ASC )
)
VAR CurrentIDRankOfTheCurrentDate =
IF (
SUM ( fct_ReqTrans[QTY] ) <> 0,
SELECTCOLUMNS (
FILTER ( ProductsOfTheCurrentDateAndRank, fct_ReqTrans[REFID] = CurrentREFID ),
"@Rank", [@CR]
)
)
VAR ProductsToFilter =
CALCULATETABLE (
SUMMARIZE ( fct_ReqTrans, fct_ReqTrans[REFID], fct_ReqTrans[REQUIREMENTDATE] ),
REMOVEFILTERS ( fct_ReqTrans[REFID] ),
DatesToUse
)
VAR ProductsToFilterAndRank =
NATURALLEFTOUTERJOIN ( ProductsToFilter, ProductsOfTheCurrentDateAndRank )
VAR ProductsFiltered =
FILTER (
ProductsToFilterAndRank,
fct_ReqTrans[REQUIREMENTDATE] <> MaxDate
|| [@CR] <= CurrentIDRankOfTheCurrentDate
)
VAR Result =
CALCULATE (
SUM ( fct_ReqTrans[QTY] ),
DatesToUse,
ALLSELECTED (),
ProductsFiltered
)
RETURN
Result
Does this satisfy you? It is rather complex but it works for me
Best,
Francesco
Original Message:
Sent: Jan 27, 2023 06:31 AM
From: Kumar Kumar
Subject: Running Total - for all columns
Dear Francesco
Thanks, it is working, but the result should below right, why it is taking whole qty for the day
------------------------------
Kumar
Original Message:
Sent: Jan 27, 2023 05:30 AM
From: Francesco Bergamaschi
Subject: Running Total - for all columns
You have filters on the top of the page, as I told you there must have been some
Use this code, regards
RT_QTY V2 =
VAR _MaxDate = MAX(fct_ReqTrans[REQUIREMENTDATE])
VAR _Result =
CALCULATE(
SUM(fct_ReqTrans[QTY]),
FILTER( ALL(fct_ReqTrans[REQUIREMENTDATE]),
fct_ReqTrans[REQUIREMENTDATE] <= _MaxDate
),
REMOVEFILTERS(dimDates),
ALLSELECTED()
)
RETURN
_Result
------------------------------
Francesco Bergamaschi
Consultant & Professor
Original Message:
Sent: Jan 27, 2023 04:32 AM
From: Kumar Kumar
Subject: Running Total - for all columns
Dear Francesco
i could not able fix it.
there are some reason i'm not using the matrix visual and i'm using Table visual (Repeat row values like in excel).
here i have attached the sample files for your advise and kindly check the measure "RT_QTY V2"
Regards
Kumar
------------------------------
Kumar Kumar
Original Message:
Sent: Jan 27, 2023 04:11 AM
From: Francesco Bergamaschi
Subject: Running Total - for all columns
Hi,
Now the last step is adding a call to VALUES ( column1, column2,...) where column1 etc are those you want to keep the filter on. There must have been a filter before that now we canceled with REMOVEFILTERS, otherwise qtys cannot be bigger than before. Pls check what filter is applied to the matrix, maybe from the filter panel or another visual. Otherwise pls send me the pbix
------------------------------
Francesco Bergamaschi
Consultant & Professor
Original Message:
Sent: Jan 27, 2023 03:12 AM
From: Kumar Kumar
Subject: Running Total - for all columns
Dear Francesco
when i include the removefilter(), i'm not getting the expected result. It is calculating all the qty in the table.
------------------------------
Kumar Kumar
Original Message:
Sent: Jan 26, 2023 10:23 AM
From: Francesco Bergamaschi
Subject: Running Total - for all columns
Hi again
In this case you should add a call to REMOVEFILTERS ( )
Let mw know if it works
Best,
------------------------------
Francesco Bergamaschi
Consultant & Professor
Original Message:
Sent: Jan 26, 2023 07:57 AM
From: Sharavan Kumar
Subject: Running Total - for all columns
Dear Francesco
I know if I remove this will work. My requirement is how can build Dax for any columns or multiple columns
Hope you understand what I am looking
Regards
Original Message:
Sent: 1/26/2023 1:41:00 AM
From: Francesco Bergamaschi
Subject: RE: Running Total - for all columns
Hi, you need to remove the filters from the columns REFID and REFTYPENAME using REMOVEFILTERS inside CALCULATE in addition to what you have already written.
Regards
------------------------------
Francesco Bergamaschi
Consultant & Professor
Original Message:
Sent: Jan 25, 2023 07:11 AM
From: Kumar Kumar
Subject: Running Total - for all columns
Team
I'm creating a sample report, to show the running total. Below dax is working fine
After adding some other dimension then the Running total is not showing as expected
What could be the reason and help me to fix this. i'm sure someone might faced this issue before.
Regards
------------------------------
Kumar Kumar
------------------------------