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 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
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.
6 Replies
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.
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?
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.
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?
No problem.