How to show the any 10 years data

[Anonymous]

Hi,

I have a report which connects to ADX data source through DirectQuery. We have data from 1950s. So, i guess my report will give performance issues with such a huge data(as the load method is DirectQuery). I want users to access the whole historic data but not all at a time. So they want to see any 10 years data at a time. 

Is there any way to have the date slicer in a "between " format and at the same time restrict the user to select only the dates before 10 years from the start date.?

Example:

If the user selects 01/01/1950 in the start date,the end date should show dates only upto 01/01/1960

If the user selects 01/01/2010 in the start date,the end date should show dates only upto 01/01/2020

Please help me.

Thanks,

Sai Priya.

[v-luwang-msft · Accepted solution]

Hi @Anonymous ,

You need  a data table and a date table ,they with no relationship between them.If you use a date in the data table as a slicer, then when you pick the value in the slicer, only one data for that date will be returned, so you need another date table.

Refer:

data_table = CALENDAR("1900,1,1","2021,1,1")

date_table = CALENDAR("1900,1,1","2021,1,1")

Then use the below measure:

TEST = 
IF (
    MAX ( 'data_table'[Date] )
        >= CALCULATE ( MIN ( 'date_table'[Date] ), ALLSELECTED ( 'date_table' ) )
        && MAX ( 'data_table'[Date] )
            <= DATE ( YEAR ( CALCULATE ( MIN ( 'date_table'[Date] ), ALLSELECTED ( 'date_table' ) ) ) + 10, MONTH ( CALCULATE ( MIN ( 'date_table'[Date] ), ALLSELECTED ( 'date_table' ) ) ), DAY ( CALCULATE ( MIN ( 'date_table'[Date] ), ALLSELECTED ( 'date_table' ) ) ) ),
    1,
    BLANK ()
)

Apply measure on filter:

Output:

When I choose   slicer:

Output maxdatevalue:

And when I choose 1922,the data table show the max date value is 1392:

You could download my pbix file if you need!

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[jaipal]

@Anonymous  follow the link. Something similar to your topic

https://community.powerbi.com/t5/Desktop/Show-the-last-X-years-based-on-the-current-Selection-of-the-Year/m-p/72162#M29920

[Anonymous]

Thanks Jaipal, I got the answer.