Cumulative Running Total Backlog PowerBI report

[Anonymous]

Hi 

I'm trying to create a PowerBI report using Azure DevOps (Analytics View) WorkItems Backlog.  I  have a calender table with default Date Column (Date/Time datatype) and Main table with Created Date , Closed Date .  Based  on some help from this community posts, I have created few measures which seems to be working except Cumulative running total . appreciate if anyone guide me what I'm missing here. 

Main Table Name >> Dashboard 

Columns used from Main Table >> Created Date, Closed Dated, Work Item Id

Date table 

DateTable = CALENDAR(MIN(Dashboard[Created Date]),MAX(Dashboard[Created Date]))

Measures:

ADOCreated = COUNTA('Dashboard'[Created Date])

ADOClosed = CALCULATE(COUNTA(Dashboard[Closed Date]), USERELATIONSHIP('DateTable'[Date], Dashboard[Closed Date]))

ADOBalance = [ADOCreated] - [ADOClosed]

ADOBacklog = CALCULATE([ADOBalance],FILTER(ALL(DateTable),DateTable[Date]<=MAX(DateTable[Date])))

But the last column where i need to display the cumulative running total doesn't seem to be working. 

[v-luwang-msft]

Hi @Anonymous ，

Has your problem been solved, if so, please consider Accept a correct reply as the solution to help others find it.

Best Regards

Lucien

[Jihwan_Kim]

Hi, @Anonymous 

Please try something like below.

ADOBacklog =
CALCULATE (
SUMX ( VALUES ( datetablemonthcolumn ), [ADOBalance] ),
FILTER ( ALL ( DateTable ), DateTable[Date] <= MAX ( DateTable[Date] ) )
)

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

[Anonymous]

Thanks. I tried and it didnt work yet. Hope you can see my reply (with more details) in this thread. It sill gives the difference between Created Count vs Closed Count

ADOBacklogCount =

CALCULATE (

SUMX ( VALUES ( Dashboard[CreatedDateMod].[Month] ), [ADOBalance] ),

FILTER ( ALL ( DateTable ), DateTable[Date] <= MAX ( DateTable[Date] ) )

)

[amitchandak]

@Anonymous , refer to my blog, create a formula same as current employee and try

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

[Anonymous]

hi @amitchauhan 

I think I referred your post from the forum earlier. Anyway, With the CurrentEmployers example I followed and it seems to be return the balance not the cumulative total yet. Not sure I'm doing something wrong with the way I define the date etc...  I have my X Axis  in the chart / Date  column in matrix is based on formated "Created Date" which is 

CreatedDateMod = FORMAT('Dashboard'[Created Date],"mm/dd/yyyy")

Here is my various try of calculating cumulative measure including your your suggestion (last one)  from  your example which I downloaded the PBIX

ADOCreated = COUNTA('Dashboard'[Created Date])

ADOClosed = CALCULATE(COUNTA(Dashboard[Closed Date]), USERELATIONSHIP('DateTable'[Date], Dashboard[Closed Date]))

ADOBalance = [ADOCreated] - [ADOClosed]

ADOBacklog = SUMX(FILTER(ALL('Calendar'),'Calendar'[Date] <= MAX('Calendar'[Date])),[ADOBalance])

ADOO = 
CALCULATE(
          COUNTx(
                 FILTER(
                           Dashboard, Dashboard[Created Date] <= MAX(DateTable[Date]) && 
                           (ISBLANK(Dashboard[Closed Date]) || 
                           Dashboard[Closed Date] > MAX(DateTable[Date]))
                       ),
                       (Dashboard[Work Item Id])
                ),
                CROSSFILTER
                (
                    Dashboard[Created Date],DateTable[Date],None
                )
         )

 
Can you let me know if I'm missing anything