CODEWITHSUNDEEP
powerbi
Bidstrup
Bidstrup

Reputation: 1617

PowerBi: Days between purchase on multiple rows

I'm trying to calculate the daydiff between when the first (H1) and the second (H2) product have been bought for each customer (ID)

I can do it if the dates are on the same line, but not on mulitple rows. There are 5.6mil rows in all with 30 different products, but right now its only between H1 and H2

purchases

The result i'm after is something like

result

Upvotes: 0

Views: 58

Answers (1)

smpa01
smpa01

Reputation: 4282

For a data source with 5.6M rows, I would only recommend doing it though native server-side query unless the calculations are not required to evaluate in respect to some filter context. Else, you can do it through the following DAX measure.

If you have a table like this and you want the DateDIff by ID for each preceding PurchaseDate

| ID | Product | PurchaseDate |
|----|---------|--------------|
| 10 | H1      | 2021-09-15   |
| 10 | H2      | 2021-09-19   |
| 20 | H1      | 2021-05-01   |
| 20 | H2      | 2021-06-05   |
| 20 | H3      | 2021-07-08   |

you can write the following measure

Measure = 
VAR _curentlyVisibleDateById =
    MAX ( 'fact'[PurchaseDate] )
VAR _immediatelyPrecedingDateById =
    CALCULATE (
        CALCULATE (
            MAX ( 'fact'[PurchaseDate] ),
            'fact'[PurchaseDate] < _curentlyVisibleDateById
        ),
        ALLEXCEPT ( 'fact', 'fact'[ID] )
    )
VAR _diffbyId =
    DATEDIFF ( _immediatelyPrecedingDateById, _curentlyVisibleDateById, DAY )
RETURN
    _diffbyId

Solution

Edit

If you want to limit calculations to only specific groups of Product; i.e. only for Products H1,H2,H3 then please use this

Measure = 
VAR _curentlyVisibleDateById =
    CALCULATE (
        MAX ( 'fact'[PurchaseDate] ),
        'fact'[Product] IN { "H1", "H2", "H3" }
    )
VAR _immediatelyPrecedingDateById =
    CALCULATE (
        CALCULATE (
            MAX ( 'fact'[PurchaseDate] ),
            'fact'[PurchaseDate] < _curentlyVisibleDateById
        ),
        ALLEXCEPT ( 'fact', 'fact'[ID] ),
        'fact'[Product] IN { "H1", "H2", "H3" }
    )
VAR _diffbyId =
    DATEDIFF ( _immediatelyPrecedingDateById, _curentlyVisibleDateById, DAY )
RETURN
    _diffbyId

Solution

Upvotes: 1

Related Questions