Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Cumulative Running Total Backlog PowerBI report

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. 
 
Need Help

5 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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] ) )
      )
  • v-luwang-msft's avatar
    v-luwang-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    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