Default view to last 13 months

[homelander123]

Hi, 

I have a report and i want to set the defualt view of the visualizations to last 13 months. There is 3 years of data in the dataset and when i apply filters on the visualization or the page, the date slicer does not show an option to select data from the date prior to last 13th month. 

Is there a way i can fix this?

[homelander123]

@PBI_learn_NK Can you please look at the newest message

[v-linyulu-msft · Accepted solution]

Thanks for the reply from@PBI_learn_NK ,please allow me to provide another insight:

Hi,@homelander123 

Regarding the issue you raised, my solution is as follows:

1.First I have created the following table and the column names and data are the data you have given:

2.Below are the measure I've created for your needs:

The first  is to include the entire month, ending on the last day of the month 13 months ago.

diff1 = 
IF ( DATEDIFF ( MAX ( 'Table'[Date] ), TODAY (), MONTH ) <= 13, 1, 0 )

The second is exactly 13 months according to the present, accurate to the day.

diff2 = 
IF ( SUM ( 'Table'[Date] ) >= EDATE ( TODAY (), -13 ), 1, 0 )

3.Then add the measure to the visualization, and then modify the visualization settings as shown in the following image:

4.Finally, the measure is removed from the column.Here's my final result, which I hope meets your requirements.

5.Here is the relevant documentation:

EDATE function - Microsoft Support

DATEDIFF (Transact-SQL) - SQL Server | Microsoft Learn

Can you share sample data and sample output in tabular format if I am misunderstanding? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

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

[homelander123]

how to ensure the slicer is not limited by the islast13months filter? 

I did apply the is last 13 months filter on one of the visuals but its the same problem since that visual does not go prior to last 13 months

[PBI_learn_NK]

Add a Date Column for Filtering:

• In your data model, create a new calculated column to flag the last 13 months. For example, if your date column is Date, you can create a new column IsLast13Months:
  IsLast13Months =
  IF(
  'YourTable'[Date] >= TODAY() - 365*3 &&
  'YourTable'[Date] <= TODAY() &&
  'YourTable'[Date] >= EDATE(TODAY(), -13),
  1,
  0
  )
  

  1. Set Up the Slicer and Filter:

     • Add the new IsLast13Months column as a slicer or visual level filter in your report.
     • Set the slicer or filter to IsLast13Months = 1 to show only the last 13 months by default.
     • This will ensure the visualizations are filtered to the last 13 months.

  2. Adjust the Date Slicer:

     • Add your original date column (Date) to a slicer.
     • Ensure this slicer is not limited by the IsLast13Months filter.

  By separating the filter logic (to set the default view) from the slicer (to provide full date range selection), you achieve both objectives: defaulting to the last 13 months while allowing users to select dates from the entire dataset.