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])``````

Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
Check out my latest book!

Announcements