Forum Discussion

12scml's avatar
12scml
Icon for Resolver I rankResolver I
9 years ago
Solved

Measure for dates until next sale

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...
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi 12scml,

     

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

    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's avatar
12scml
Icon for Resolver I rankResolver I
9 years ago

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's avatar
    v-ljerr-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    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. :smileyhappy:

    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's avatar
      12scml
      Icon for Resolver I rankResolver I
      9 years ago

      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's avatar
        v-ljerr-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        Hi 12scml,

         

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

        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