Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Filter out consecutive null values

Hello,   How can I create a slicer(or other means) to ignore Products which have not been sold consecutively in the last three days ?               I have the following table:     Then I...
Need Help
Show and Tell
  • MFelix's avatar
    MFelix
    5 years ago

    Hi Anonymous ,

     

    You can try the following formula:

    Sales in the last 3 days =
VAR saleslast3days =
    CALCULATE (
        SUM ( Sales[Units Sold] ),
        DATESBETWEEN ( Sales[Date], MAX ( Sales[Date] ) - 2, MAX ( Sales[Date] ) )
    )
RETURN
    IF ( saleslast3days > 0, saleslast3days )

     

    And the result is below:

    If you want this to be dinamic based on a selection of dates you need to have a disconnected table for the filtering and then redo your measure using that table:

     

    Sales in the last 3 days = 
VAR saleslast3days =
    CALCULATE (
        SUM ( Sales[Units Sold] ),
        DATESBETWEEN ( Sales[Date], MAX ( Dates[Date] ) - 2, MAX ( Dates[Date] ) )
    )
RETURN
    IF ( saleslast3days > 0, saleslast3days )

     

    Check PBIX file with both options.

     

     

MFelix's avatar
MFelix
Super User
5 years ago

Hi Anonymous ,

 

You can try the following formula:

Sales in the last 3 days =
VAR saleslast3days =
    CALCULATE (
        SUM ( Sales[Units Sold] ),
        DATESBETWEEN ( Sales[Date], MAX ( Sales[Date] ) - 2, MAX ( Sales[Date] ) )
    )
RETURN
    IF ( saleslast3days > 0, saleslast3days )

 

And the result is below:

If you want this to be dinamic based on a selection of dates you need to have a disconnected table for the filtering and then redo your measure using that table:

 

Sales in the last 3 days = 
VAR saleslast3days =
    CALCULATE (
        SUM ( Sales[Units Sold] ),
        DATESBETWEEN ( Sales[Date], MAX ( Dates[Date] ) - 2, MAX ( Dates[Date] ) )
    )
RETURN
    IF ( saleslast3days > 0, saleslast3days )

 

Check PBIX file with both options.

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi MFelix, 

     

    Could you please clarify on what you meant by

     

    "if you want this to be dinamic based on a selection of dates you need to have a disconnected table for the filtering and then redo your measure using that table"

     

    what table do i need to create for this? 

    • MFelix's avatar
      MFelix
      Super User
      5 years ago

      Hi Anonymous 

       

      sorry wrote faster than I think about the phrase. If you check my PBIX file I have a table with only dates.

       

      You need to create a date table with all the dates and used it has your slicer.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi MFelix ,

     

    I have another question, which is almost like an extension of this problem. Just out of courtesy, would you mind if I tag you into it if I post it as a separate community question?