cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MrSujay
Resolver II
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..

 

MrSujay_0-1630523187308.png

 

 

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
MrSujay
Resolver II
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

 

 

View solution in original post

2 REPLIES 2
MrSujay
Resolver II
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

 

 

View solution in original post

Greg_Deckler
Super User
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!

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors