DAX

[PriscillaCamp]

Hi, I am trying to create a backlog column for my monthly volume of tickets. I have an open measure and a closed measure. Their formulas to create these measures are:

Opened = CALCULATE(COUNT(VolumeForRitmOpenandClosedTickets[RitmSysCreatedOn - Without Time]), USERELATIONSHIP(VolumeForRitmOpenandClosedTickets[RitmSysCreatedOn - Without Time], 'VolumeCalendar'[Date]))

Closed = CALCULATE(COUNT([RitmClosedAt - Without Time] ), FILTER(VolumeForRitmOpenandClosedTickets,VolumeForRitmOpenandClosedTickets[RitmState]="Closed Complete")  ,USERELATIONSHIP(VolumeForRitmOpenandClosedTickets[RitmClosedAt - Without Time],'VolumeCalendar'[Date]))

What I would like to do is take the Backlog from the previous month and add (subtract) from the remainder (result of open – closed) for the current month. So for Oct 16…. Open - Close is 549 - 477 which leave a remainder of 72. I then want to take the backlog from last month (Sept 2016) and add it to the remainder to get the backlog for this Oct (72 +15) =84 backlog

The only exception would be for the first month - the formula would be a little bit different to kick it off. For July 2016 only, I would like to take the remaining column and put this value in the backlog field.

I included a picture, which may help explain easier what I am trying to say. I hope I was clear enough.

If you would like the PBIX file, I can send it to your email. Thank you in advance. 

[v-diye-msft · Accepted solution]

Hi @PriscillaCamp 

Please kindly check whether you'd like to get below results: (It's will be much helpful if you can attach the copyable table next time)

1.  Insert an index column in power query to keep the data sequence 

2. Use below measure:

Measure 2 = SUMX(FILTER(ALL(TableA),[Index]<=MAX(TableA[Index])),[Remaining])

 

Note there're miscalculation in your previous capture.

[v-diye-msft]

Hi @PriscillaCamp 

Please kindly paste your excel data here that I can copy it,  or preferable a pbix that I can draw it up for you.

[PriscillaCamp]

Hi,

My file is from SQL Server. I do not know how to attach a PBIX to this message. Is there a place where I put it on your one drive?

Thanks.

[v-diye-msft]

Hi @PriscillaCamp 

1. You can upload your file to Onedrive/Dropbox, and share the link here. (Make sure the connection mode is Import, I can't view it on my side if it is in Direct query mode.

2. Alternatively, copy the table shown in the previous table, and paste it here with table format. that I can copy it.

[PriscillaCamp]

Just to clarify -  the day column should be a year column in the picture