locked
DAX-Sequencing Continous Days in One bucket RRS feed

  • Question

  • Hi All,

    We are facing a requirement where we would like to sequence Continuous days into one bucket. 

    Ex : Table 

    Product,Campaign Date,Sales
    P1,JAN 1 2017,100

    P1,JAN 2 2017,200

    P1,JAN 3 2017,300

    P1,JAN 15 2017,400

    P1,JAN 16 2017,500

    Campaign End is identified by a rule , if difference between Date Next Entry in table for Campaign in comparison with previous row is greater than 1 then its a new campaign. In the above example difference between 4th row in the table and 3rd row is greater than 1. We need output in the below format :

    PRODUCT,CAMPAIGN STARTDATE,CAMPAIGN END DATE

    P1,JAN 1 2017,JAN 3 2017

    P1,JAN 15 2017,JAN 16 2017

    Thanks in advance.

    Regards

    SD

    Monday, June 5, 2017 7:01 AM

Answers

  • If a Power Query solution is also fine, then you can use the following code:

    let
        Source = Table1,
    
        // Add indices to merge the table with itself to get the dates from the previous row on the same row as the current date
        #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
        #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1),
        #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index"},#"Added Index1",{"Index.1"},"Previous",JoinKind.LeftOuter),
        #"Expanded Previous" = Table.ExpandTableColumn(#"Merged Queries", "Previous", {"Campaign Date"}, {"Previous.Campaign Date"}),
        #"Sorted Rows" = Table.Sort(#"Expanded Previous",{{"Index", Order.Ascending}}),
    
        // Now the startdate of each campaign can be determined:
        #"Added Custom" = Table.AddColumn(#"Sorted Rows", "Campaign StartDate", each if [Campaign Date] - #duration(1,0,0,0) <> [Previous.Campaign Date] then [Campaign Date] else null),
    
        // Merge the table with itself to get the next date on the same row as the current date:
        #"Merged Queries1" = Table.NestedJoin(#"Added Custom",{"Index.1"},#"Added Custom",{"Index"},"Next",JoinKind.LeftOuter),
        #"Expanded Next" = Table.ExpandTableColumn(#"Merged Queries1", "Next", {"Campaign Date"}, {"Next.Campaign Date"}),
        #"Added Custom1" = Table.AddColumn(#"Expanded Next", "Campaign EndDate", each if [Campaign Date] + #duration(1,0,0,0) <> [Next.Campaign Date] then [Campaign Date] else null),
    
        // Fill down the start dates and fill up the end dates:
        #"Filled Down" = Table.FillDown(#"Added Custom1",{"Campaign StartDate"}),
        #"Filled Up" = Table.FillUp(#"Filled Down",{"Campaign EndDate"}),
    
        // Finishing touches:
        #"Removed Other Columns" = Table.SelectColumns(#"Filled Up",{"Product", "Campaign StartDate", "Campaign EndDate"}),
        #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns")
    in
        #"Removed Duplicates"


    Monday, June 5, 2017 8:58 AM