Running Sum Bar Chart Help

[jarwest]

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.

[v-zhangti · Accepted solution]

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))+0

SoldActual = 
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))+0

New 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.

[amitchandak]

@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-tr...
Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZU

Date == 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-bd52912a5b...
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.