Display data for past 2 years from selected date in slicer

[Vamshi2020]

Hi All,

I have one Date Dimension Table and one Fact Table. Iam using year from Date Dimension table as slicer. My requirement is ,
what ever year i select in slicer, my fact table visual should show the data for past 24 months from selected year. I have assigned date in fact table, so data should show the past 24 months of assigned date.

FYI, Dim Date and Fact table are not related.

Please help me to achieve this.

TIA

[v-jayw-msft · Accepted solution]

Hi @Vamshi2020 ,

Refer the following steps.

Create a measure like below.

Measure = 
var data_year = YEAR(SELECTEDVALUE('Table'[date]))
return
IF(data_year<=SELECTEDVALUE('date'[year])&&data_year>SELECTEDVALUE('date'[year])-2,1,0)

Add it to visual level filter.

The result would be shown as below.

Best Regards,

Jay

[yaseenhamid]

hi @v-jayw-msft , its not working for me.  

i have 2 tables....

-one is dimension table ('Table1' with Date column)

-second is fact table ('scurve with cummulative' with Date column)

-fact and dimension tables are connected via date columns

-my slicer has date column from table 1 

-my area chart has x axis & y-axis (date column & cummulative cost- both from 'scurve with cummulative' table)

- if i select a month (eg. september) from slicer...i want the graph to show values of semtember and all values before that month. Please help

[runatthakorn]

I try to use but measure shown 1 on selected value only. Please advise me.

Measure = 
VAR YearSelection = SELECTEDVALUE(dim_Date[Year])
RETURN
IF(YearSelection <= MAX(vvip_bi_data_all[spend_year]) && YearSelection > MAX(vvip_bi_data_all[spend_year])-2, 1, 0)

 

[amitchandak]

@Vamshi2020 ,  a measure like

2 year =
var _min = minx(allselected('Date'), 'Date'[Date])
var _max = maxx(allselected('Date'), 'Date'[Date])
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Table'),'Table'[Date] >= _min && 'Table'[Date] <=_max))

Also REfer

https://www.youtube.com/watch?v=duMSovyosXE

[Vamshi2020]

@amitchandak ,

I want to restrict all the columns in Table visual to show data for past 24 months.