Filter out consecutive null values

[Anonymous]

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 needed an output table that ONLY adds the number of units sold of products which have not been zero in the last three days

Output table:

Thanks,

John

[MFelix · Accepted solution]

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]

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?

[MFelix]

No problem.

[Anonymous]

Thank you so much Miguel. I have tagged you in here How to filter out rows from an output table whose ... - Microsoft Power BI Community ?

[Anonymous]

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]

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.