cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bsheffer
Helper V
Helper V

Running total of selected months

What I want is like a running total in a visual with a legend of year and an axis of months.  The user picks the months they want from a page filter and for each month that appears I want to show the total of that month and all the previously appearing months.

 

so if I choose March, June, and september from the filter, only 3 months appear in the visual.  I want march to show just the total for that month.  June shows the total of both March and June.  September shows the total of March, June & September.

 

this is only if the user chooses a selection of months.  If the user doesn't use the filter or chooses all 12 months in a year, I want it to show the standard running total.

 

bsheffer_0-1638565404283.png

 

bsheffer_2-1638566588890.png

the two visuals are identical except for the _measure in the values field

 

here is the page filter

bsheffer_1-1638565529815.png

both the page filter and the visual axis are using the same field, so getting a count of the number of distinct values selected in the filter is difficult for me since the visual restricted it to just one monthly value

 

the monthy values are 

Month mmmYear_Total_Partner_Payment
Jan202110938
Feb20219962
Mar2021-22864
Apr20211571
May20213763
Jun20212409
Jul20217089
Aug20212309
Sep20213307
Oct20214573
Nov20210

 

so march should show -22864

    june should show -20455 (-22864 + 2409)

   sep should show -17148  (-22864 + 2409 + 3307)

 

as contrasted to the normal running totals

Month mmmYear_running_total_Partner_Payment
Jan202110938
Feb202120900
Mar2021-1963
Apr2021-392
May20213370
Jun20215779
Jul202112868
Aug202115177
Sep202118483
Oct202123056
Nov202123056

 

Ideally I would have the ability to test for the number of months selected.  (0 or 12 would use the standard running total and any number of months between 1 to 11 would just total the selected months on or before that month on the visual for that year.

 

here are my current measures

 

_running_total_Partner_Payment =
var _year = max('Dim Calendar ActivityMonth'[Year])
var _month= max('Dim Calendar ActivityMonth'[MonthNo])
return
if(ISBLANK([_Total_Partner_Payment]), BLANK(),
CALCULATE('Fact Cubes'[_Total_Partner_Payment],
year('Dim Calendar ActivityMonth'[Date])= _year,
month('Dim Calendar ActivityMonth'[Date])<= _month
)
)
 
_Total_Partner_Payment = sum('Fact Cubes'[Total_Partner_Payment])
1 ACCEPTED SOLUTION
VijayP
Super User
Super User

@bsheffer 
I think you want this way ! attached the pbix file 

VijayP_0-1638590701241.png

 




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


View solution in original post

2 REPLIES 2
VijayP
Super User
Super User

@bsheffer 
I think you want this way ! attached the pbix file 

VijayP_0-1638590701241.png

 




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


View solution in original post

thanks for your help.  Here is my modified measure.  My changes in bold.  The key was using the allselected function.  It ignored the implicit month filter in the visual while still respecting the month page filter values.

 

_running_total_Partner_Payment =
var _year = max('Dim Calendar ActivityMonth'[Year])
var _month= max('Dim Calendar ActivityMonth'[MonthNo])
var _months_selected = calculatetable(VALUES('Dim Calendar ActivityMonth'[MonthNo]), allselected('Dim Calendar ActivityMonth'))
return
if(ISBLANK([_Total_Partner_Payment]), BLANK(),
CALCULATE('Fact Cubes'[_Total_Partner_Payment],
year('Dim Calendar ActivityMonth'[Date])= _year,
month('Dim Calendar ActivityMonth'[Date])<= _month
,month('Dim Calendar ActivityMonth'[Date]) in _months_selected
)
)

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

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!

Top Solution Authors
Top Kudoed Authors