Apply a date filter by default

[Anonymous]

Hi guys,

When the user will open the report, all visuals should be filtered to display only data of the last year WHICH HAS CONTENTS. 

by exemple: we are in 2022, if this year has data, we filter by 2022 if not by 2021.

But I must use the slicer as below to allow user to select a date range specific

The mesure checks if the slicer is used or not. At the opening of the report, it's not used so it will count the number of cars of the last year which has contents

number of car sold = 
var x = ISFILTERED(Periode[Date])
var y = IF(x,DISTINCTCOUNT(car[N° car]),TOTALYTD(DISTINCTCOUNT(car[N° car]),Periode[Date]))
return y

x is false when 2 bounds of the slicer are at 2 extremities as circled in red like the screen above

The solution doesn't work because if the user would like to see data of all time, he will let the bounds at 2 extremities and the mesure will just count for the last year which has contents.

We thouht about saved the report with the slicer filtered by the last year and readapt the mesure but we will must change each year, it's not useful.

I hope that the requirement is clear for you and thank you in advance for the help ! 

Have a good day 🙂

[v-janeyg-msft · Accepted solution]

Hi, @Anonymous 

In the date slider, there is no filtered date and the date slider is an effect on the border, isfiltered function can't distinguish between the two cases.

In your cases, you can use 'enter data' in desktop to create a table like this, and use it as a slicer.

Then create a measure to distinguish whether there is a filter date.

Like this:

number of car sold =
IF (
    SELECTEDVALUE ( 'table 2'[Date selected] = "No" ),
    DISTINCTCOUNT ( car[N° car] ),
    TOTALYTD ( DISTINCTCOUNT ( car[N° car] ), Periode[Date] )
)

Did I answer your question? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,

Community Support Team _Janey

[Anonymous]

@amitchandak it doesn't meet the requirement. please tell me where is not clear in my post

[amitchandak]

@Anonymous , YTD and LYTD based on today

YTD Today=
var _min = date(year(today()),1,1)
var _day = today()
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Date] >=_min && 'Date'[Date] <= _day) )

LYTD =
var _min = date(year(today())-1,1,1)
var _max = date(year(today())-1,month(today()),day(today()))
var _day = datediff(_min, _max,day)+1
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year] = year(Today())-1 && 'Date'[Day of Year] <= _max))

YTD Today=
var _min = if(month(today()) >=7, date(year(today()),7,1), date(year(today())-1,7,1) )
var _day = today()
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Date] >=_min && 'Date'[Date] <= _day) )

LYTD =
var _min = if(month(today()) >=7,date(year(today())-1,7,1) ,date(year(today())-2,7,1))
var _max = date(year(today())-1,month(today()),day(today()))
var _day = datediff(_min, _max,day)+1
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year] = year(Today())-1 && 'Date'[Day of Year] <= _max))

YTD=
var _min = date(year(today()),1,1)
var _day = datediff(_min, today(),day)+1
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year] =year(Today()) && 'Date'[Day of Year] <= _day) )

LYTD =
var _min = date(year(today())-1,1,1)
var _max = date(year(today())-1,month(today()),day(today()))
var _day = datediff(_min, _max,day)+1
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year] = year(Today())-1 && 'Date'[Day of Year] <= _day))

LYTD Today =
var _min = eomonth(today(),-2)+1
var _max = date(year(today()),month(today())-1,day(today()))
var _day = datediff(_min, _max,day)+1
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Date] >= _min && 'Date'[Date] <=_max && 'Date'[Day of Year] <= _day))

In the case of datesytd and totalytd, they are dependent on the date selected. Or on the last date in the calendar when nothing is selected

Default Date Today/ This Month / This Year: https://www.youtube.com/watch?v=hfn05preQYA