Creating the DAX formula to use the previous value in the other column

[Anonymous]

Hello everyone

I'm trying to create the backlog column as follows:

Backlog= (OpenTicket + Backlog pre_week )- CloseTicket

I know the Backlog= 2 per week (24 Feb - 1 Sea )

Date	OpenTicket	CloseTicket	Backlog
02-08 Mar	5	2	5
09-15 Mar	4	3	6
16 -22 Mar	6	2	10

Could someone else give me a solution to this? It would help me a lot.

Thank you in advance,

[amitchandak]

@Anonymous 

you have to build it using open and close

Cumm Sales = CALCULATE([OPen]-[Close],filter(date,date[date] <=maxx(date,date[date])))
Cumm Sales = CALCULATE([OPen]-[Close],,filter(date,date[date] <=max(Sales[Sales Date])))

Or use week Rank, if you do not have Dates

Assume Week table is Date

Cumm Sales = CALCULATE([OPen]-[Close],filter(date,date[Week Rank] <=maxx(date,date[Week Rank])))

How to do

https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...

[Anonymous]

@amitchandak Thanks for your answer, I created a week number and I'm using that to compare dates, now what I need, is calculate Backlog column by using this formula:

Backlog= (OpenTicket - CloseTicket) + pre_week Backlog

for example now for week 10, it should be 56

Thanks

[v-kelly-msft · Accepted solution]

Hi  @Anonymous ,

If you need a calculated column,see below:

Column = 
var _preweekbacklog=CALCULATE(MAX('Table'[Backlog]),FILTER('Table','Table'[Date]=EARLIER('Table'[Date])-7))
Return
'Table'[OpenTicket]+_preweekbacklog-'Table'[CloseTicket]

And you will see:

Or you can create a measure as below:

Measure = 
var _preweekbacklog=CALCULATE(MAX('Table'[Backlog]),FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date])-7))
Return
MAX('Table'[OpenTicket])+_preweekbacklog-MAX('Table'[CloseTicket])

Finally you will see:

For the related .pbix file,pls click here.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

@amitchandak Thanks for your answer, I created a week number and I'm using that to compare dates, now what I need, is calculate Backlog column by using this formula:

Backlog= (OpenTicket - CloseTicket) + pre_week Backlog

for example now for week 10, it should be 56 

Thanks