CALC Column to get next date from fact table?

[Anonymous]

Hi, I have a dimension table called Customers, and a fact table called Sales. These are in an SSAS Tabular model.

I want to add a calculated column onto dbo customers called "next bike sale" which will show me the NEXT SalesDate from today per customer, for all productkeys of 2. So the output on the above would look like the below

Can anyone help me achieve this, some of the solutions I found online suggested using the related function but it wont work (i think because customers table filters sales table)

[v-henryk-mstf · Accepted solution]

Hi @Anonymous ,

According to your description, if you want to get the second date after each grouping according to the customer field, you can refer to the following formula: first sort the dates according to the grouping. Then filter out the dates that meet the conditions:

rank_ = 
    RANKX (
        FILTER ( ALL ( Sales ), Sales[customer_key] = EARLIER ( Sales[customer_key] ) ),
        [sales_date],
        ,
        ASC,

Col_data = CALCULATE (
        MAX ( Sales[sales_date] ),
        FILTER (
            ALL ( Sales ),
            Sales[rank_] = 2
                && Sales[customer_key] = EARLIER ( Sales[customer_key] )
        )
    )

If the problem is still not resolved, please point it out. Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@Anonymous Seems like: Column = MINX(FILTER('FactTabe',[ProductKey]=2),[SalesDate])

[Anonymous]

Thanks @Greg_Deckler .. this only seems to bring back the minimum date from the table and not the minimum date per customer?

[Anonymous]

nevermind I added && customer.customerkey = sales.customerkey to the filter and I think this has worked

[Greg_Deckler]

@Anonymous Oh, I was assuming that the tables were related to one another on customerkey. Yes, if that is not the case then you will need that additional clause.