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

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

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

Thank you so much! That worked like a charm!