Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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,
Solved! Go to 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
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,
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
@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