Calculate backlog from 2 dates with m code or dax

Question

CSV file data:

Ticket ID,In Date,Out Date
INC-005,null,20/12/2022
INC-005,null,22/12/2022
INC-009,null,null
INC-011,null,null
INC-013,null,null
INC-013,null,null
INC-015,null,null
INC-015,null,null
INC-019,null,null
INC-020,null,null
INC-020,null,null
INC-021,null,null
INC-022,null,null
INC-027,null,null
INC-027,null,null
INC-029,null,null
INC-029,null,null
INC-036,null,null
INC-036,null,null
INC-038,null,null
INC-039,null,null
INC-040,null,null
INC-040,null,null
INC-041,null,null
INC-044,null,null
INC-044,null,null
INC-045,null,null
INC-045,null,null
INC-046,null,null
INC-046,null,null
INC-047,null,null
INC-048,null,null
INC-049,null,null
INC-084,null,null
INC-087,null,null
INC-087,null,null
INC-088,null,null
INC-092,null,null
INC-092,null,null
INC-095,null,null
INC-096,null,null
INC-097,null,null
INC-103,null,null
INC-105,null,null
INC-105,null,null
INC-106,null,null
INC-113,null,null
INC-118,null,null
INC-130,null,null
INC-130,null,null
INC-132,null,null
INC-141,null,null
INC-149,null,null
INC-151,null,null
INC-151,null,null
INC-165,null,null
INC-165,null,null
INC-201,null,null
INC-204,null,null
INC-205,null,null
INC-208,null,null
INC-213,null,null
INC-216,null,null
INC-217,null,null
INC-225,null,null
INC-225,null,null
INC-233,null,null
INC-233,null,null
INC-234,null,null
INC-234,null,null
INC-241,null,null
INC-247,null,null
INC-247,null,null
INC-248,null,null
INC-262,null,null
INC-264,null,null
INC-267,null,null
INC-271,null,null
INC-273,null,null
INC-274,null,null
INC-276,null,null
INC-278,null,null
INC-278,null,null
INC-279,null,null
INC-279,null,null
INC-290,null,null
INC-290,null,null
INC-292,null,null
INC-298,null,null
INC-304,null,null
INC-304,null,null
INC-315,null,21/12/2022
INC-317,null,null
INC-321,null,null
INC-321,null,null
INC-323,null,null
INC-323,null,null
INC-328,null,null
INC-331,null,null
INC-333,null,null
INC-335,null,null
INC-339,null,null
INC-341,null,null
INC-341,null,null
INC-342,null,null
INC-344,null,null
INC-347,null,null
INC-355,null,null
INC-356,null,null
INC-357,null,null
INC-358,null,null
INC-358,null,null
INC-360,null,null
INC-362,null,null
INC-362,null,null
INC-385,null,null
INC-386,null,null
INC-387,null,null
INC-388,null,null
INC-403,null,null
INC-409,null,null
INC-446,null,null
INC-452,null,null
INC-453,null,null
INC-459,null,null
INC-461,null,null
INC-461,null,null
INC-466,null,null
INC-468,null,null
INC-468,null,null
INC-472,null,null
INC-476,null,null
INC-476,null,null
INC-477,null,null
INC-481,null,null
INC-485,null,null
INC-485,null,null
INC-494,null,null
INC-495,null,null
INC-496,null,null
INC-505,null,null
INC-515,null,null
INC-515,null,null
INC-524,null,null
INC-534,null,null
INC-540,null,null
INC-551,null,null
INC-553,null,null
INC-556,null,null
INC-558,null,null
INC-559,null,null
INC-574,null,null
INC-578,null,20/12/2022
INC-582,null,null
INC-584,null,null
INC-588,null,null
INC-588,null,null
INC-592,null,null
INC-595,null,null
INC-600,null,null
INC-600,null,null
INC-602,null,null
INC-602,null,null
INC-609,null,null
INC-609,null,null
INC-613,null,null
INC-617,null,null
INC-632,null,null
INC-633,null,null
INC-638,null,null
INC-639,null,null
INC-640,null,null
INC-640,null,null
INC-641,null,null
INC-641,null,null
INC-650,null,null
INC-656,null,null
INC-669,null,null
INC-680,null,null
INC-698,null,null
INC-708,null,null
INC-708,null,null
INC-709,null,null
INC-713,null,null
INC-716,null,null
INC-717,null,null
INC-726,null,null
INC-728,null,null
INC-729,null,null
INC-732,null,null
INC-732,null,null
INC-737,null,null
INC-762,null,null
INC-762,null,null
INC-763,null,null
INC-773,null,null
INC-780,null,null
INC-780,null,null
INC-781,null,null
INC-790,null,null
INC-791,null,null
INC-793,null,null
INC-793,null,null
INC-794,null,null
INC-795,null,null
INC-811,null,null
INC-811,null,null
INC-813,null,null
INC-813,null,null
INC-815,null,null
INC-815,null,null
INC-820,null,null
INC-820,null,null
INC-103,25/12/2022,null
INC-006,23/12/2022,23/12/2022
INC-026,23/12/2022,23/12/2022
INC-038,23/12/2022,null
INC-082,23/12/2022,23/12/2022
INC-094,23/12/2022,23/12/2022
INC-097,23/12/2022,null
INC-134,23/12/2022,23/12/2022
INC-140,23/12/2022,23/12/2022
INC-149,23/12/2022,23/12/2022
INC-149,23/12/2022,23/12/2022
INC-152,23/12/2022,23/12/2022
INC-161,23/12/2022,23/12/2022
INC-170,23/12/2022,23/12/2022
INC-190,23/12/2022,23/12/2022
INC-191,23/12/2022,23/12/2022
INC-192,23/12/2022,23/12/2022
INC-193,23/12/2022,23/12/2022
INC-194,23/12/2022,23/12/2022
INC-195,23/12/2022,23/12/2022
INC-207,23/12/2022,23/12/2022
INC-224,23/12/2022,23/12/2022
INC-229,23/12/2022,23/12/2022
INC-245,23/12/2022,null
INC-245,23/12/2022,23/12/2022
INC-274,23/12/2022,23/12/2022
INC-274,23/12/2022,23/12/2022
INC-290,23/12/2022,23/12/2022
INC-301,23/12/2022,23/12/2022
INC-310,23/12/2022,23/12/2022
INC-320,23/12/2022,23/12/2022
INC-321,23/12/2022,23/12/2022
INC-336,23/12/2022,23/12/2022
INC-366,23/12/2022,23/12/2022
INC-410,23/12/2022,23/12/2022
INC-411,23/12/2022,23/12/2022
INC-412,23/12/2022,23/12/2022
INC-413,23/12/2022,23/12/2022
INC-414,23/12/2022,23/12/2022
INC-415,23/12/2022,23/12/2022
INC-416,23/12/2022,23/12/2022
INC-417,23/12/2022,23/12/2022
INC-418,23/12/2022,23/12/2022
INC-419,23/12/2022,23/12/2022
INC-420,23/12/2022,23/12/2022
INC-421,23/12/2022,23/12/2022
INC-422,23/12/2022,23/12/2022
INC-423,23/12/2022,23/12/2022
INC-424,23/12/2022,23/12/2022
INC-425,23/12/2022,23/12/2022
INC-426,23/12/2022,23/12/2022
INC-427,23/12/2022,23/12/2022
INC-428,23/12/2022,23/12/2022
INC-429,23/12/2022,23/12/2022
INC-430,23/12/2022,23/12/2022
INC-431,23/12/2022,23/12/2022
INC-432,23/12/2022,23/12/2022
INC-434,23/12/2022,23/12/2022
INC-467,23/12/2022,23/12/2022
INC-471,23/12/2022,23/12/2022
INC-483,23/12/2022,23/12/2022
INC-485,23/12/2022,23/12/2022
INC-491,23/12/2022,23/12/2022
INC-492,23/12/2022,23/12/2022
INC-494,23/12/2022,null
INC-511,23/12/2022,23/12/2022
INC-512,23/12/2022,23/12/2022
INC-513,23/12/2022,23/12/2022
INC-533,23/12/2022,23/12/2022
INC-538,23/12/2022,null
INC-540,23/12/2022,null
INC-548,23/12/2022,23/12/2022
INC-581,23/12/2022,23/12/2022
INC-597,23/12/2022,23/12/2022
INC-598,23/12/2022,23/12/2022
INC-598,23/12/2022,23/12/2022
INC-613,23/12/2022,null
INC-625,23/12/2022,23/12/2022
INC-626,23/12/2022,23/12/2022
INC-627,23/12/2022,23/12/2022
INC-628,23/12/2022,23/12/2022
INC-688,23/12/2022,23/12/2022
INC-689,23/12/2022,23/12/2022
INC-690,23/12/2022,23/12/2022
INC-691,23/12/2022,23/12/2022
INC-692,23/12/2022,23/12/2022
INC-697,23/12/2022,23/12/2022
INC-704,23/12/2022,23/12/2022
INC-705,23/12/2022,23/12/2022
INC-777,23/12/2022,23/12/2022
INC-778,23/12/2022,23/12/2022
INC-779,23/12/2022,23/12/2022
INC-786,23/12/2022,23/12/2022
INC-790,23/12/2022,null
INC-795,23/12/2022,null
INC-819,23/12/2022,23/12/2022
INC-820,23/12/2022,null
INC-827,23/12/2022,23/12/2022
INC-017,22/12/2022,null
INC-017,22/12/2022,22/12/2022
INC-025,22/12/2022,22/12/2022
INC-031,22/12/2022,22/12/2022
INC-064,22/12/2022,22/12/2022
INC-065,22/12/2022,22/12/2022
INC-066,22/12/2022,22/12/2022
INC-067,22/12/2022,22/12/2022
INC-068,22/12/2022,22/12/2022
INC-073,22/12/2022,22/12/2022
INC-077,22/12/2022,22/12/2022
INC-086,22/12/2022,22/12/2022
INC-093,22/12/2022,22/12/2022
INC-115,22/12/2022,22/12/2022
INC-117,22/12/2022,22/12/2022
INC-117,22/12/2022,22/12/2022
INC-121,22/12/2022,22/12/2022
INC-124,22/12/2022,22/12/2022
INC-133,22/12/2022,22/12/2022
INC-139,22/12/2022,22/12/2022
INC-144,22/12/2022,22/12/2022
INC-146,22/12/2022,22/12/2022
INC-147,22/12/2022,22/12/2022
INC-148,22/12/2022,22/12/2022
INC-150,22/12/2022,22/12/2022
INC-155,22/12/2022,22/12/2022
INC-160,22/12/2022,22/12/2022
INC-166,22/12/2022,22/12/2022
INC-188,22/12/2022,22/12/2022
INC-189,22/12/2022,22/12/2022
INC-197,22/12/2022,22/12/2022
INC-200,22/12/2022,22/12/2022
INC-206,22/12/2022,22/12/2022
INC-208,22/12/2022,23/12/2022
INC-208,22/12/2022,22/12/2022
INC-211,22/12/2022,22/12/2022
INC-212,22/12/2022,22/12/2022
INC-213,22/12/2022,null
INC-214,22/12/2022,22/12/2022
INC-216,22/12/2022,22/12/2022
INC-216,22/12/2022,22/12/2022
INC-216,22/12/2022,22/12/2022
INC-221,22/12/2022,22/12/2022
INC-227,22/12/2022,22/12/2022
INC-237,22/12/2022,22/12/2022
INC-248,22/12/2022,null
INC-251,22/12/2022,23/12/2022
INC-253,22/12/2022,22/12/2022
INC-254,22/12/2022,22/12/2022
INC-256,22/12/2022,22/12/2022
INC-259,22/12/2022,22/12/2022
INC-260,22/12/2022,22/12/2022
INC-281,22/12/2022,22/12/2022
INC-284,22/12/2022,22/12/2022
INC-291,22/12/2022,22/12/2022
INC-294,22/12/2022,22/12/2022
INC-295,22/12/2022,22/12/2022
INC-308,22/12/2022,22/12/2022
INC-311,22/12/2022,22/12/2022
INC-317,22/12/2022,null
INC-318,22/12/2022,22/12/2022
INC-319,22/12/2022,22/12/2022
INC-337,22/12/2022,22/12/2022
INC-338,22/12/2022,22/12/2022
INC-360,22/12/2022,null
INC-380,22/12/2022,22/12/2022
INC-403,22/12/2022,null
INC-404,22/12/2022,22/12/2022
INC-405,22/12/2022,22/12/2022
INC-406,22/12/2022,22/12/2022
INC-407,22/12/2022,22/12/2022
INC-408,22/12/2022,22/12/2022
INC-409,22/12/2022,null
INC-441,22/12/2022,22/12/2022
INC-441,22/12/2022,22/12/2022
INC-445,22/12/2022,22/12/2022
INC-452,22/12/2022,null
INC-454,22/12/2022,22/12/2022
INC-457,22/12/2022,22/12/2022
INC-460,22/12/2022,23/12/2022
INC-474,22/12/2022,22/12/2022
INC-486,22/12/2022,22/12/2022
INC-489,22/12/2022,22/12/2022
INC-506,22/12/2022,22/12/2022
INC-507,22/12/2022,22/12/2022
INC-508,22/12/2022,22/12/2022
INC-509,22/12/2022,22/12/2022
INC-510,22/12/2022,22/12/2022
INC-517,22/12/2022,22/12/2022
INC-522,22/12/2022,22/12/2022
INC-523,22/12/2022,23/12/2022
INC-563,22/12/2022,22/12/2022
INC-572,22/12/2022,22/12/2022
INC-575,22/12/2022,22/12/2022
INC-579,22/12/2022,22/12/2022
INC-580,22/12/2022,22/12/2022
INC-580,22/12/2022,22/12/2022
INC-580,22/12/2022,22/12/2022
INC-596,22/12/2022,22/12/2022
INC-603,22/12/2022,22/12/2022
INC-606,22/12/2022,22/12/2022
INC-607,22/12/2022,22/12/2022
INC-608,22/12/2022,22/12/2022
INC-608,22/12/2022,22/12/2022
INC-611,22/12/2022,22/12/2022
INC-612,22/12/2022,22/12/2022
INC-616,22/12/2022,22/12/2022
INC-633,22/12/2022,null
INC-675,22/12/2022,22/12/2022
INC-676,22/12/2022,22/12/2022
INC-677,22/12/2022,22/12/2022
INC-678,22/12/2022,22/12/2022
INC-679,22/12/2022,22/12/2022
INC-680,22/12/2022,null
INC-681,22/12/2022,22/12/2022
INC-682,22/12/2022,22/12/2022
INC-683,22/12/2022,22/12/2022
INC-684,22/12/2022,22/12/2022
INC-685,22/12/2022,22/12/2022
INC-686,22/12/2022,22/12/2022
INC-687,22/12/2022,22/12/2022
INC-695,22/12/2022,22/12/2022
INC-700,22/12/2022,22/12/2022
INC-701,22/12/2022,22/12/2022
INC-702,22/12/2022,22/12/2022
INC-703,22/12/2022,22/12/2022
INC-712,22/12/2022,22/12/2022
INC-724,22/12/2022,22/12/2022
INC-725,22/12/2022,22/12/2022
INC-775,22/12/2022,22/12/2022
INC-776,22/12/2022,22/12/2022
INC-781,22/12/2022,null
INC-784,22/12/2022,22/12/2022
INC-803,22/12/2022,22/12/2022
INC-804,22/12/2022,22/12/2022
INC-823,22/12/2022,22/12/2022
INC-008,21/12/2022,21/12/2022
INC-018,21/12/2022,21/12/2022
INC-060,21/12/2022,21/12/2022
INC-061,21/12/2022,21/12/2022
INC-062,21/12/2022,21/12/2022
INC-063,21/12/2022,21/12/2022
INC-070,21/12/2022,21/12/2022
INC-071,21/12/2022,21/12/2022
INC-075,21/12/2022,22/12/2022
INC-075,21/12/2022,21/12/2022
INC-085,21/12/2022,21/12/2022
INC-089,21/12/2022,21/12/2022
INC-096,21/12/2022,null
INC-104,21/12/2022,21/10/2022
INC-114,21/12/2022,21/12/2022
INC-115,21/12/2022,21/12/2022
INC-116,21/12/2022,21/12/2022
INC-119,21/12/2022,21/12/2022
INC-120,21/12/2022,21/12/2022
INC-126,21/12/2022,21/12/2022
INC-127,21/12/2022,21/12/2022
INC-128,21/12/2022,21/12/2022
INC-128,21/12/2022,21/12/2022
INC-137,21/12/2022,21/12/2022
INC-138,21/12/2022,21/12/2022
INC-142,21/12/2022,21/12/2022
INC-167,21/12/2022,21/12/2022
INC-167,21/12/2022,21/12/2022
INC-169,21/12/2022,21/12/2022
INC-182,21/12/2022,21/12/2022
INC-183,21/12/2022,21/12/2022
INC-184,21/12/2022,21/12/2022
INC-185,21/12/2022,21/12/2022
INC-186,21/12/2022,21/12/2022
INC-187,21/12/2022,21/12/2022
INC-187,21/12/2022,21/12/2022
INC-198,21/12/2022,21/12/2022
INC-230,21/12/2022,21/12/2022
INC-231,21/12/2022,21/12/2022
INC-232,21/12/2022,21/12/2022
INC-238,21/12/2022,null
INC-238,21/12/2022,21/12/2022
INC-238,21/12/2022,21/12/2022
INC-243,21/12/2022,21/12/2022
INC-249,21/12/2022,21/12/2022
INC-255,21/12/2022,21/12/2022
INC-261,21/12/2022,21/12/2022
INC-269,21/12/2022,21/12/2022
INC-269,21/12/2022,21/12/2022
INC-270,21/12/2022,21/12/2022

The ticketing tool we have gives us an export with lines of every action we did, something like that:

Ticket ID |    In Date    |    Out Date    |
INC001    |    1/1/2023   |    2/1/2023    | ->
INC001    |    3/1/2023   |    null        | -> transferred back or reopened
INC001    |    null       |    4/1/2023    | -> closed or transferred
INC002    |    3/1/2023   |    3/1/2023    | -> arrived and closed/transferred on same day
INC003    |    3/1/2023   |    null        | -> ticket was sent back/reopened
INC004    |    3/1/2023   |    null        | -> incoming
INC004    |    null       |    null        | -> example: just added a comment
INC005    |    4/1/2023   |    5/1/2023    | ->

I need to have a number of the daily backlog which is difficult because there are several lines with the same ticket ID, and even after closing or transferring a ticket, if sent back or reopened it needs to go back into the backlog:

Date      | Backlog |
1/1/2023  |    1    |
2/1/2023  |    0    |
3/1/2023  |    3    |
4/1/2023  |    3    |

Using M code is there a way to do that? Or maybe with Dax?

I have searched and tried but am about to give up :P.

Anyone got an idea of how I could achieve this?

Thank you

Ron Rosenfeld · Accepted Answer

If I have properly deduced your logic, the following should work. At least it works on your data sample and on some variations I devised. But if your data sample is not truly representative, code may require modifications:

Code Edited to account for:

multiple nulls and null in both In Date and Out Date
Error where Out Date is prior to In Date

let
    Source = Csv.Document(File.Contents("C:\Users\ron\Desktop\New Text Document (3).txt"),[Delimiter=",", Columns=3, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{
        {"Ticket ID", type text}, {"In Date", type date}, {"Out Date", type date}}, "en-GB"),

//Correct error where Out Date is earlier than In Date
    #"Error Correct" = Table.FromRecords(Table.TransformRows(#"Changed Type",
                        (r) => Record.TransformFields(r,
                             {"Out Date", each 
                                if r[In Date] = null or _ = null then _
                                else if r[In Date] > _ then r[In Date] else _}))),
                        
                        

maxDate = List.Max(#"Error Correct"[In Date] & #"Error Correct"[Out Date]),
minDate = List.Min(#"Error Correct"[In Date] & #"Error Correct"[Out Date]),

    #"Grouped Rows" = Table.Group(#"Error Correct", {"Ticket ID"}, {

//Remove out dates from the possible list of dates in service
//This may need to be modified if your sample is not representative of your actual data
        {"Open Dates", (t)=>
            let

            //IF first In Date or last Out Date is null, then replace with minDate or MaxDate
            // then fill down

                #"Filled In Dates" =  Table.FillDown(Table.ReplaceValue(Table.FirstN(t,1),null,minDate,Replacer.ReplaceValue,{"In Date"})
                     & Table.Skip(t,1),{"In Date"}),

                #"Fill Out Dates" = Table.FillUp(Table.RemoveLastN(#"Filled In Dates",1) & 
                    Table.ReplaceValue(Table.LastN(#"Filled In Dates",1),null,maxDate,Replacer.ReplaceValue,{"Out Date"}),{"Out Date"}),

                #"Date Ranges" = Table.AddColumn(#"Fill Out Dates", "dateRange", 
                    each List.Dates([In Date], Duration.Days([Out Date]-[In Date]), #duration(1,0,0,0))),

                theDates = List.Distinct(List.Combine(#"Date Ranges"[dateRange]))
            in 
                theDates
        }
        }),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Ticket ID"}),
    #"Expanded Open Dates" = Table.ExpandListColumn(#"Removed Columns", "Open Dates"),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Open Dates", each ([Open Dates] <> null)),

//create date table for joining
    allDates = Table.FromList(List.Dates(minDate,Duration.Days(maxDate-minDate), #duration(1,0,0,0)),
                        Splitter.SplitByNothing(), type table[Dates=date]),

//Join with data table
    join = Table.Join(#"Filtered Rows","Open Dates", allDates,"Dates", JoinKind.FullOuter),
    #"Grouped Rows1" = Table.Group(join, {"Dates"}, {
        {"Backlog", each List.Count(List.RemoveNulls([Open Dates])), Int64.Type}
    }),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows1",{{"Dates", Order.Ascending}})
in
    #"Sorted Rows"

Original data

From CSV snippet

Calculate backlog from 2 dates with m code or dax

Answers (1)

Related Questions