Forum Discussion
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...
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
Super User
Super UserHi 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.
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?