Days Bracket Filter in Report

[sudhakar111]

Dear All,

I need help in creating a days bucket formula, which will be used in the report as a filter.

Below is the code i wrote.The problem with this is when bracket 30 is selected it shows only dates which are above 7. It does not show dates below 7 days. Same with 60 days,it shows dates above 30 days.

Date_Condition = Max(Last_Execution_Date)-Last_Execution_Date

Days_Filter = if(('Exchange'[Date_Condition]>=1 &&'Exchange'[Date_Condition]<=7),7,if(('Exchange'[Date_Condition]>=1 && 'Exchange'[Date_Condition]<=30),30,if(('Exchange'[Date_Condition]>=1 && 'Exchange'[Date_Condition]<=60),60)))

[sudhakar111]

Thank you both for your response. Please find the link to sample information. User Name and Last Activity date have to be displayed in the table. Based on the slicer selection,the user and last activity date have to be filtered.

https://onedrive.live.com/?id=1F5A3C32EB7ABDBA%21104&cid=1F5A3C32EB7ABDBA 

[V-pazhen-msft]

@sudhakar111 

You cannot do that by creating a calculate column using IF(). Try the following method:

1. Create a "Slicer Table" with Enter Data, enter value, 7,30,60. 

2. Create a measure. 

Measure = CALCULATE(MAX([Date_Condition]),FILTER('Table',[Date_Condition]<=SELECTEDVALUE('Slicer Table'[Slicer])))

3. Create a slicer with the Slicer column from the new table to filter. 

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sudhakar111]

@V-pazhen-msft  Created the measure and slicer. When a range is selected in the filter it is also displaying other ranges,and they are showing as empty. When a selection is made, other ranges should not be displayed on the table.

[V-pazhen-msft]

@sudhakar111 

I cannot access to the onedrive link you provided.

Paul

[AllisonKennedy]

You may need to use custom measures for the values, rather than just normal sums in this case.

If I understand your question correctly you may be looking for something similar to a running total, that when 30 is selected, displays all 30 days or less.

There are a few ways to do this, see if this link helps, otherwise please share table and column names for your data and a desired result.

https://www.sqlbi.com/articles/computing-running-totals-in-dax/