DATEADD measure as a filter is not working.

[Yubo]

Hello,

I tried to use this formula as a filter to get report for 2 days. 

Filter = if(

    SELECTEDVALUE(Final[Start Date])>=SELECTEDVALUE(Final[Report RunDate]) &&

    SELECTEDVALUE(Final[Start Date])<= DATEADD(Final[Report RunDate], 2,day) ,1,0   )

even if  I can see some [Start Date] there between Report RunDate and Report RunDate+2 days, it shows all 0.

Please help.

Many thanks, 🙂

[amitchandak]

@Yubo , Where are you using this. If it is measure use something like this

Date Period =
var _max =maxx(allselected(date),date[date])
var _min =minx(allselected(date,date[date]))-2

return
CALCULATE(SUM(Sales[Sales Amount]),filter(all(date),date[date]=_max || date[date]=_min))

[Yubo]

Hi Amitchandak,

  I use it to filter the report, I put it in the report for test and will put it as Visual-level filter =1.

Please see the attachment. 

Many thanks,

[mahoneypat]

Dateadd() returns a table of Date (even if just 1), so not sure you would get True in this application.  You could just try Final[ReportRunDate] + 2 instead.  You are going for a table visual with Dates and 1 or 0s?

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Yubo]

Hi Pat,

It is not working. +2

Thanks for reply. 🙂

[mahoneypat]

Can you provide some sample data and desired output (is this measure going in a card or a table?)?  Hard to tell what you are trying to accomplish.

Regards,

Pat

[Yubo]

Hi Pat,

I don't want to put it in the report, will use it as visual-level filter to have the report only including those days records,

for example, I want the report only showing the data=06/05.2020. 

Many thanks,

[Anonymous]

Hi @Yubo,

If you want to achieve a specific filter range instead of directly filter on your records, please use unrelated table fields as the source of the filter.

Measure =
VAR currDate =
    MAX ( Final[Start Date] )
RETURN
    IF (
        currDate
            IN CALENDAR (
                MINX ( ALLSELECTED ( 'Calendar'[Date] ), [Date] ),
                MAXX ( ALLSELECTED ( 'Calendar'[Date] ), [Date] ) + 2
            ),
        1,
        0
    )

Notice: 'Calendar' is a date table that not related to raw table records.

Regards,

Xiaoxin Sheng

[Yubo]

Hi Xiaoxin,

I set up releationship bwtween final table and calendar table, but as you said, it is still not working, I tried the code, the dates I got are not what I want, I may study your coding later.I appreciate it.

Many thanks for your help! 🙂