Measure for dates until next sale

[12scml]

Hi All!
I have two tables: Registration and Sales. Both tables regions as attributes and are related by a singular date column. I would like to make a measure that calculates how many days there are until the next sale when someone registers. In other words, if someone registers 2 days before a sale, it will give 2, or if they register on the same day as a sale, it will give 0. Does anyone know how I might be able to do this? Additionally, I would like to have the measure give the number of days until the next sale in the same region.

Thanks!!

[12scml]

Hi All! Thank you for looking into my problem! I have attached some screenshots of sample data and what I am hoping it will give! 

The current relationships are Date Registered *-1 All Dates and Date of Sale *->1 All Dates.

Please let me know if this isn't clear and I will upload different images! Thanks so much!

[v-ljerr-msft]

Hi @12scml,

Based on my test, you should be able to use the formula below to create a calculate column in your Registration table to calculate the Days Until Next Sale in this scenario. 

Days Until Next Sales = 
VAR firstSalesDate =
    CALCULATE (
        MIN ( Sales[Date of Sales] ),
        FILTER ( ALL ( Sales ), Sales[Name] = Registration[Name] )
    )
RETURN
    IF (
        ISBLANK ( firstSalesDate ),
        BLANK (),
        DATEDIFF ( Registration[Date Registered], firstSalesDate, DAY )
    )

Regards

[12scml]

Hi @v-ljerr-msft!
Thank you so much for your help! I'm afraid I may not have been clear enough with my problem though. I actually am trying to find the days until next sale independently of the name. I just want to find the days until the next sale by anyone! For example, Consider the case where John registers on the 2nd and has a sale on the 8th, while wendy registers on the 3rd and has a sale on the 4th. Then I want John's Days Until Next Sale to be 2, and Wendy's to be 1. 

An additional constraint that I forgot to mention before is that I want this to calculate the days between sales only based on the registration and the sale being in the same region. 

Sorry for the confusion! But thanks for your help so far 🙂 

[v-ljerr-msft · Accepted solution]

Hi @12scml,

If I understand you correctly, the formula below should work in your scenario. 

Days Until Next Sales =
VAR firstSalesDate =
    CALCULATE (
        MIN ( Sales[Date of Sales] ),
        FILTER (
            ALL ( Sales ),
            Sales[Date of Sales] >= Registration[Date Registered]
                && Sales[Region] = Registration[Region]
        )
    )
RETURN
    IF (
        ISBLANK ( firstSalesDate ),
        BLANK (),
        DATEDIFF ( Registration[Date Registered], firstSalesDate, DAY )
    )

Regards

[12scml]

Thank you so much! That worked like a charm!

[hemantsingh]

hi @12scml,

   I agree with @v-ljerr-msft, kindly provide some sample data. Things gets more clear onl;y after getting hands on with data.

Regards

[v-ljerr-msft]

Hi @12scml,

Could you post your table structures with some sample data and your expected result, so that we can better assist on this issue?

Regards