Dynamic Date IF Statement

[Anonymous]

Hi, 

I'm trying to do a simple IF Statment but it doesn't seem to work; I'm trying to created a calculate column to use as a customer filter. 

I'm looking at 3 Columns as listed below:

1. (Measure) Date_Selected = CALCULATE(MAX('DATE'[Date]),ALLSELECTED('DATE'))
2. dwh InventoryTenancy'[Start] = Colum from data set
3. dwh InventoryTenancy'[End] = Colum from data set

In the below screen shot the highlighed "Exclude" should be "Include". 

Any help is always appricated. 

Thanks 

Dobby Libr3 

[v-juanli-msft]

Hi @Anonymous 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case and help the other members find it more quickly?

If not, please feel free to let me know.

Best Regards

Maggie

[Anonymous]

Hi Maggie, 

Still not resolved, I have tired out all the Measures in the chat and also looked at the model, I can't figure out why its not picking up, Also my data set has less than 20,000 line and the refresh on visuals takes far too long for the simple calculations. 

there is a picture of my model, date table is not connected... 

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Do you check my answers above?

these are all measures instead of columns

max selected = MAX('calendar'[Date])

Measure = IF([max selected]>MAX([start])&&[max selected]<=MAX([end]),"Include","exclude")

Best Regards

Maggie

[Anonymous]

Hi, 

This worked after testing again! Thank you very much. 

Regards 

Dobby Libr3

[FrankAT]

Hi,

try the following measure:

Report Filter = 
IF (
    MIN ( 'dwh InventoryTenancy'[Start] )
        > CALCULATE ( MIN ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) ) &&
    MIN ( 'dwh InventoryTenancy'[End] )
        <= CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) ),
    "Include",
    "Exclude"
)

Regards FrankAT

[Anonymous]

Hi @FrankAT, 

thanks for your input, I currently getting the insufficent memory error, ( I have 32gb RAM and am connecting to a datawarehouse) so I don't think its really a memory issue.  

[nandukrishnavs]

Hi @Anonymous ,

Slicers cannot be used in calculated columns. Instead, you can use measure.

Date_Selected = CALCULATE(MAX('DATE'[Date]),ALLSELECTED('DATE'))

ReportFilter =
VAR selectedDate = 'Date'[Date_Selected]
VAR result =
    CALCULATE (
        IF (
            selectedDate > SELECTEDVALUE ( Inventory[Start] )
                && selectedDate <= SELECTEDVALUE ( Inventory[End] ),
            "Include",
            "Exclude"
        )
    )
RETURN
    result

snapshot

If required you apply a visual level filter.

Regards,

Nandu Krishna

[Anonymous]

Hi @nandukrishnavs , 

Unfortantly this bough back the same results I got where it seems to not be applying the logic. 

Thanks 

Rob 

[v-juanli-msft]

Hi @Anonymous 

It seems your slicer set as "before",

so the range you select is date 2017/10/1~2018/9/1

For the first line 2018/8/1~2018/11/12, it is not include in the selected date range.

Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[FrankAT]

Hi @Anonymous,

are your Start and End date true calendar dates? If they are text it doesn't work!

Regards FrankAT

[Anonymous]

Hi @FrankAT 

Yeah these are for sure Calender dates! 

@v-juanli-msft  I tried changing the date slicer but this returned the same result! also this shouldn't make a diffrence as the Dax is going off the [Date_Selected} Column not the acutal slicer... 

Thanks 

Rob

[v-juanli-msft]

Hi @Anonymous 

I don't know what causes your problem.

On my side, if i have tables without relationships, then create measures

add "date" from "calendar" table to the slicer,

max selected = MAX('calendar'[Date])

Measure = IF([max selected]>MAX([start])&&[max selected]<=MAX([end]),"Include","exclude")

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

try this?

Report Filter = 

var _selectedDate = CALCULATE(max('Calendar'[Date]),ALLSELECTED('Calendar'[Date]))

var IncExc = if( _selectedDate > calculate(firstdate('Table'[from]),'Table') 
              && _selectedDate <= calculate(firstdate('Table'[to]),'Table'),
            "Include",
            "Exclude"
)

return IncExc