Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Anonymous
Not applicable

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

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 )

DateOpenTicketCloseTicketBacklog
02-08 Mar525
09-15 Mar436
16 -22 Mar6210

 

 

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

 

Thank you in advance,

1 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:

Annotation 2020-05-05 150405.png

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:

Annotation 2020-05-05 150637.png

For the related .pbix file,pls click here.

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

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

 
Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

@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

 

 

Screenshot_3.png

 

Thanks

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:

Annotation 2020-05-05 150405.png

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:

Annotation 2020-05-05 150637.png

For the related .pbix file,pls click here.

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Anonymous
Not applicable

@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 

Screenshot_3.png

 

 

Thanks

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.