A table visual should skip few records based on date slicer

[Anonymous]

Hi Community, @amitchandak 

i am working on a power BI usecase, where i have table visual and there i want to show entire period(min(Sales Date) to max(Sales Date)) data, but the ask is when i select a date from slicer, it should show all the records for entire period(min(Sales Date) to max(Sales Date)) and skip 90 days data from the selected date from slicer. 

so i need after selected date all records and before date all records also but last 90 days data should be skipped from before portion.

consider this is a Sales_Date column 

thanks in advance

[Anonymous · Accepted solution]

Hi @Anonymous ,

1. Create a new calculation table based on the date column of the original table.

Table =
DISTINCT('financials'[Date])

2. Create a measure to get dates other than the last 90 days.

Measure =
CALCULATE(sum('financials'[ Sales]),FILTER('financials','financials'[Date]>=MAX('Table'[Date]) || 'financials'[Date] < MAX('Table'[ Date]) - 90))

3. Use the date column in the calculation table as a slicer to create a visual.

You can view more information in the attached pbix file and modify the expression appropriately according to your own table. Another point worth noting is that the first 90 days may not include a complete three months, which can be modified appropriately with the help of the EOMONTH function.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hi @Anonymous ,

1. Create a new calculation table based on the date column of the original table.

Table =
DISTINCT('financials'[Date])

2. Create a measure to get dates other than the last 90 days.

Measure =
CALCULATE(sum('financials'[ Sales]),FILTER('financials','financials'[Date]>=MAX('Table'[Date]) || 'financials'[Date] < MAX('Table'[ Date]) - 90))

3. Use the date column in the calculation table as a slicer to create a visual.

You can view more information in the attached pbix file and modify the expression appropriately according to your own table. Another point worth noting is that the first 90 days may not include a complete three months, which can be modified appropriately with the help of the EOMONTH function.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Thanks for your response i also did the same