cancel
Showing results for
Did you mean:
Resolver II

## Running total for measure without date colum

I want to run the cumulative running sum/total in table visual.
Here Fiscal month is a column and Monthly Comm Earned is a measure which includes another measure also.
now I want to calculate the sum of the running total in the sales running column.

like march will have only march amount and in April contains march + April amount and so on..

``````Monthly Comm Earned =
IF(
ISFILTERED(Transactions[Fiscal Month]),
[MonthlyCommEarnedCalc],
SUMX(
VALUES(Transactions[Fiscal Month]), [MonthlyCommEarnedCalc]
)
)``````

I Tried the below query but not working.

``````Sales Running =
Var MaxMonth = Max(Transactions[Fiscal Month Nbr])
Var Result =  CALCULATE(
Transactions[Monthly Comm Earned] ,
Transactions[Fiscal Month nbr]<= MaxMonth,
All(Transactions[Fiscal Month Nbr])
)
Return Result``````

April month should return value 8560.98 in sales running column/measure.

1 ACCEPTED SOLUTION
Resolver II

Finally, I got the solution to calculate the running total for measures without a date column.

To calculate the running total for another measure then just add that measure code ( if block ) into your running total code.

example code :

``````Monthly Comm running sum =
VAR month = MAX(Transactions[Fiscal Month Nbr])
VAR result =  CALCULATE(
IF( ISFILTERED(Transactions[Fiscal Month]),
[MonthlyCommEarnedCalc],
SUMX(VALUES(Transactions[Fiscal Month]), [MonthlyCommEarnedCalc])
),
Transactions[Fiscal Month] <= month,
ALLSELECTED(Transactions)
)
Return result``````

2 REPLIES 2
Resolver II

Finally, I got the solution to calculate the running total for measures without a date column.

To calculate the running total for another measure then just add that measure code ( if block ) into your running total code.

example code :

``````Monthly Comm running sum =
VAR month = MAX(Transactions[Fiscal Month Nbr])
VAR result =  CALCULATE(
IF( ISFILTERED(Transactions[Fiscal Month]),
[MonthlyCommEarnedCalc],
SUMX(VALUES(Transactions[Fiscal Month]), [MonthlyCommEarnedCalc])
),
Transactions[Fiscal Month] <= month,
ALLSELECTED(Transactions)
)
Return result``````

Super User

@MrSujay You will need something that defines "before", like a fiscal year/month column 202101, 202102, etc. Then you can do something like:

``````Measure =
VAR __Current = MAX('Transactions[Fiscal Month nbr])
VAR __Table = FILTER(ALL('Table'),[Fiscal Month nbr] <= __Current)
RETURN
SUMX(__Table,[Monthly Comm Earned])``````

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

Announcements