Forum Discussion
Running Sum Bar Chart Help
Hey all,
I'm working on making a bar graph that will display the running sum of items bought and sold each quarter.
I don't have a good example of sample data, but I have 3 columns I think I can perform this task with.
item - string, name/description of the item
id - string, unique identifier. is not null
bought - datetime, displays the date the item was purchased. is not null
sold - datetime, displays the date the item was sold. If null, item was never sold/needs to be sold.
so a tiny example of this data would be
| Item | ID | bought | sold |
| 4x6 bolt | 1 | 1/2/22 | 3/12/23 |
| 8x12 bolt | 2 | 1/23/23 | 2/23/23 |
| 3" washer | 3 | 2/10/23 | |
| 2" bradford nail | 4 | 4/1/23 | 4/5/23 |
| 2x4 pressed board | 5 | 7/2/23 |
The number of rows would determine the quantity of items. For example, if 3 rows have a bought value of 3/14/23, that means 3 items were bought on 3/14/23.
My end goal is creating a bar chart displaying the running sum of sold items over the course of each quarter/year.
I believe I can easily make this visualziation if I can conver the data above into a format like this below
| Year | Quarter | BoughtActual | BoughtRunning | SoldActual | SoldRunning |
| 2023 | 1 | 3 | 3 | 2 | 2 |
| 2023 | 2 | 1 | 4 | 1 | 3 |
| 2023 | 3 | 1 | 5 | 0 | 3 |
I need to find a statement to create a calculated table that takes in data from table 1 and makes it match table 2.
I believe I'd be using SUMMARIZE() for this, but I'm not sure how to populate out this formula properly, and im having significant trouble finding resources online for this situation.
The nearest I've gotten was
newTable = summarize(oldTable, "year", YEAR(bought), "quarter", QUARTER(bought), ...)
I've been struggling for the past 6 hours to find what to put in for the remaining columns to get the calculations I want.
Any and all help would be greatly appreciated.
Hi, jarwest
You can try the following methods.
Date table:
Measure:
BoughtActual = Var _minQdate=CALCULATE(MIN('Date'[Date]),ALLEXCEPT('Date','Date'[Quarter])) Var _maxQdate=CALCULATE(MAX('Date'[Date]),ALLEXCEPT('Date','Date'[Quarter])) Return CALCULATE(COUNT('Table'[ID]),FILTER(ALL('Table'),[bought]>=_minQdate&&[bought]<=_maxQdate))+0SoldActual = Var _minQdate=CALCULATE(MIN('Date'[Date]),ALLEXCEPT('Date','Date'[Quarter])) Var _maxQdate=CALCULATE(MAX('Date'[Date]),ALLEXCEPT('Date','Date'[Quarter])) Return CALCULATE(COUNT('Table'[ID]),FILTER(ALL('Table'),[sold]>=_minQdate&&[sold]<=_maxQdate))+0New table:
New table = SUMMARIZE('Date','Date'[Year],'Date'[Quarter],"BoughtActual",[BoughtActual],"SoldActual",[SoldActual])Column:
BoughtRunning = SUMX(FILTER('New table',[Quarter]<=EARLIER('New table'[Quarter])),[BoughtActual])SoldRunning = SUMX(FILTER('New table',[Quarter]<=EARLIER('New table'[Quarter])),[SoldActual])Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
jarwest , Make sure bought and sold do not have timestamp else create dates without timestamp
example new column
bought date = datevalue([bought])
Join both of them with a date table having year, qtr, month etc. One join will be inactive, that you can activate using userealtionship
Assume bought is active .
measures
m1= countrows(Table)
Using sold date
calculate( countrows(Table) ,USERELATIONSHIP ('Table'[sold], 'Date'[Date]))
refer
HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZUDate == Addcolumns(calendar(date(2012,01,01), date(2024,12,31) ), "Month no" , month([date])
, "Year", year([date])
, "Month Year", format([date],"mmm-yyyy")
, "Month year sort", year([date])*100 + month([date])
, "Qtr Year", format([date],"yyyy-\QQ")
, "Qtr", quarter([date])
, "Month",FORMAT([Date],"mmmm")
, "Month sort", month([DAte])
, "FY Year", if( Month(([DAte])) <7 , year(([DAte]))-1 ,year(([DAte])))
, "Is Today" ,if([Date]=TODAY(),"Today",[Date]&"")
,"Day of Year" , datediff(date(year([DAte]),1,1), [Date], day)+1
, "Month Type", Switch( True(),
eomonth([Date],0) = eomonth(Today(),-1),"Last Month" ,
eomonth([Date],0)= eomonth(Today(),0),"This Month" ,
Format([Date],"MMM-YYYY") )
,"Year Type" , Switch( True(),
year([Date])= year(Today()),"This Year" ,
year([Date])= year(Today())-1,"Last Year" ,
Format([Date],"YYYY")
)
)Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.Hi, jarwest
You can try the following methods.
Date table:
Measure:
BoughtActual = Var _minQdate=CALCULATE(MIN('Date'[Date]),ALLEXCEPT('Date','Date'[Quarter])) Var _maxQdate=CALCULATE(MAX('Date'[Date]),ALLEXCEPT('Date','Date'[Quarter])) Return CALCULATE(COUNT('Table'[ID]),FILTER(ALL('Table'),[bought]>=_minQdate&&[bought]<=_maxQdate))+0SoldActual = Var _minQdate=CALCULATE(MIN('Date'[Date]),ALLEXCEPT('Date','Date'[Quarter])) Var _maxQdate=CALCULATE(MAX('Date'[Date]),ALLEXCEPT('Date','Date'[Quarter])) Return CALCULATE(COUNT('Table'[ID]),FILTER(ALL('Table'),[sold]>=_minQdate&&[sold]<=_maxQdate))+0New table:
New table = SUMMARIZE('Date','Date'[Year],'Date'[Quarter],"BoughtActual",[BoughtActual],"SoldActual",[SoldActual])Column:
BoughtRunning = SUMX(FILTER('New table',[Quarter]<=EARLIER('New table'[Quarter])),[BoughtActual])SoldRunning = SUMX(FILTER('New table',[Quarter]<=EARLIER('New table'[Quarter])),[SoldActual])Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
