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 + pre_week to backlog)- CloseTicket

I know that the delay 2 per week (24 Feb - 1 Sea )

Date	OpenTicket	CloseTicket	Delay
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,

[v-lid-msft]

Hi @Anonymous ,

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

Best regards,

[v-lid-msft · Accepted solution]

Hello @negarabi ,

If OpenTicket and CloseTicket are measured and shown in the table visual, the @amitchandak solution is great and will work fine.

(when you create the measure, try using FILTER(ALLSELECTED('Date'),......) instead of FILTER('Date',......) )

But if the raw table looks like, as [Date] column is text, we can create a calculated column to get the first date this week

First Date =
VAR MonthTable =
    ADDCOLUMNS (
        GENERATESERIES ( 1, 12 ),
        "MonthName", FORMAT ( DATE ( 2000, [Value], 1 ), "MMM" )
    )
RETURN
    DATE ( 2020, MAXX ( FILTER ( MonthTable, [MonthName] = RIGHT ( [Date], 3 ) ), [Value] ), VALUE ( LEFT ( [Date], 2 ) ) )

you can beat 2020 as your year column

Next, we can create another calculation column, use the sum function if the Openticket is a column instead measure, to get the desired result:

Back Log =
VAR currentDate = [First Date]
RETURN
    2
        + CALCULATE (
            SUM ( 'Table'[OpenTicket] ) - SUM ( 'Table'[CloseTicket] ),
            'Table',
            'Table'[First Date] <= currentDate
        )

By the way, pbIX file as attachment.

Best regards

[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-YTD-LYTD-Week-Over-Week/m-p/1051626#M184