Caclulate the prior order date

[JEB85]

Hi,

An extract of my data in powerpivot is the first 3 columns of the following:

Order Date	Customer	Count	Prior Date with a Count
01/04/2016	A	0
28/04/2016	A	1	28/04/2016
30/06/2016	A	0
15/07/2016	A	1	28/04/2016
01/04/2016	B	1	01/04/2016
05/05/2016	B	1	01/04/2016
18/06/2016	B	1	05/05/2016
20/07/2016	B	0

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

For each customer where the 'count' is 1, I need to reference the prior order date where the count was also 1 (in current row context).

The prior date will be the current order date for the first record per customer (again where the count is 1).

The desired result is shown in the 'Prior Date with a Count' field.

Can this be achieved in powerpivot?

thanks

 

[Comfy]

Oh ****!

Got a solution in Power Query and then read you wanted it for PP, sorry!

I'll post anyway in case you can use for your pivot:

let
Source2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUNzDRNzIwNFPSUXIEYgOlWJ1oJSMLNGFDsLCxgb6BGaZqQ1N9A3NM1ShmOyGEgapNMYUNLZDMRggbGSCZ7QSxMhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [OrderDate = _t, Customer = _t, Count = _t]),
FilteredRows2 = Table.SelectRows(Source2, each [Count] <> "0"),
AddedIndex2 = Table.AddIndexColumn(FilteredRows2, "Index", 0, 1),
GroupedRows2 = Table.Group(AddedIndex2, {"Customer"}, {{"Values", each _, type table}}),
Sorted2 = Table.TransformColumns(GroupedRows2,{{"Values", each Table.AddIndexColumn(Table.Sort(_, "Index"),"GroupIndex")}}),
Expanded2 = Table.ExpandTableColumn(Sorted2, "Values", {"OrderDate", "Count", "GroupIndex"}, {"OrderDate", "Count", "GroupIndex"}),


//Source1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUNzDRNzIwNFPSUXIEYgOlWJ1oJSMLNGFDsLCxgb6BGaZqQ1N9A3NM1ShmOyGEgapNMYUNLZDMRggbGSCZ7QSxMhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [OrderDate = _t, Customer = _t, Count = _t]),
#"Added Index" = Table.AddIndexColumn(Source2, "Index", 0, 1),
#"Merged Queries" = Table.NestedJoin(#"Added Index",{"Customer", "OrderDate"},Expanded2,{"Customer", "OrderDate"},"NewColumn",JoinKind.LeftOuter),
#"Expanded NewColumn1" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"GroupIndex"}, {"GroupIndex"}),
#"Added Custom" = Table.AddColumn(#"Expanded NewColumn1", "pIndex", each if [GroupIndex] = null then null else List.Max({[GroupIndex]-1,0})),
#"Merged Queries1" = Table.NestedJoin(#"Added Custom",{"Customer", "pIndex"},Expanded2,{"Customer", "GroupIndex"},"NewColumn",JoinKind.LeftOuter),
#"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries1", "NewColumn", {"OrderDate"}, {"PrevOrderDate"}),
#"Sorted Rows" = Table.Sort(#"Expanded NewColumn",{{"Index", Order.Ascending}}),
#"Removed Other Columns" = Table.SelectColumns(#"Sorted Rows",{"OrderDate", "Customer", "Count", "PrevOrderDate"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"OrderDate", type date}, {"Count", Int64.Type}, {"PrevOrderDate", type date}})
in
#"Changed Type"

Edit: Lightbulb moment, I realised I didn't need to hold the source in memory twice (Source2 and Source1)

 

[Ozeroth]

Hi JEB85,

Here are some ideas for different versions of DAX:

Prior Date with a Count (Latest DAX)
=
IF (
    YourTable[Count] = 1,
    VAR PrevDate =
        CALCULATE (
            MAX ( YourTable[Order Date] ),
            ALLEXCEPT ( YourTable, YourTable[Customer], YourTable[Count] ),
            YourTable[Order Date] < EARLIER ( YourTable[Order Date] )
        )
    RETURN
        IF ( ISBLANK ( PrevDate ), YourTable[Order Date], PrevDate )
)

Prior Date with a Count (Old DAX v1)
=
IF (
    YourTable[Count] = 1,
    CALCULATE (
        MIN ( YourTable[Order Date] ),
        FILTER (
            ALL ( YourTable[Order Date] ),
            YourTable[Order Date]
                >= CALCULATE (
                    MAX ( YourTable[Order Date] ),
                    ALLEXCEPT ( YourTable, YourTable[Customer], YourTable[Count] ),
                    YourTable[Order Date] < EARLIER ( YourTable[Order Date], 2 )
                )
        ),
        ALLEXCEPT ( YourTable, YourTable[Customer], YourTable[Count] )
    )
)

Prior Date with a Count (Old DAX v2)
=
IF (
    YourTable[Count] = 1,
    IF (
        ISBLANK (
            CALCULATE (
                MAX ( YourTable[Order Date] ),
                ALLEXCEPT ( YourTable, YourTable[Customer], YourTable[Count] ),
                YourTable[Order Date] < EARLIER ( YourTable[Order Date] )
            )
        ),
        YourTable[Order Date],
        CALCULATE (
            MAX ( YourTable[Order Date] ),
            ALLEXCEPT ( YourTable, YourTable[Customer], YourTable[Count] ),
            YourTable[Order Date] < EARLIER ( YourTable[Order Date] )
        )
    )
)

Owen

 

[JEB85]

Thank you both for your solutions! I will test them tomorrow and met you know the results!

comfy, where would I put in your code (I'm not that familiar with power query)?

 

[Comfy]

Thank you both for your solutions! I will test them tomorrow and met you know the results!

comfy, where would I put in your code (I'm not that familiar with power query)?

You paste it into the advanced editor: https://support.office.com/en-us/ar...er-Query-9f08ceac-c949-4408-baff-6a7eaa183e11

 

[JEB85]

Both those solutions work brilliantly - thanks a lot!