Filter data based on Dynamic date without adding filter column.

[mohtism]

Hey everyone,

I have a requirement where user will select a date (dont have any relation with table) and data will be filtered accordingly based on following condition selected date<=effectivefrom  and selected date>effectiveto.

I have created the measure and its working fine as long as effective from and effective to columns are there as shown in visual (1)
But in report requirment these two columns should not be in visual as shown in view (2) it should only contain Division Code & Area Code in Matrix Visual.

So when i remove them result changes as shown in view (2).

So is there any way to achieve it?

Effort that i did:

I did applied it using direct query but its too slow and i want it done in import mode.Also dont want other approach which is hiding the column by turning of wrap text as reports will be sent as in excel.

[johnt75 · Accepted solution]

Try

Sum of quantity =
VAR ChosenDate =
    SELECTEDVALUE ( 'Date'[Date] )
VAR Result =
    SUMX (
        FILTER (
            'Table',
            ChosenDate <= 'Table'[Effective from]
                && ChosenDate > 'Table'[Effective to]
        ),
        'Table'[Quantity]
    )
RETURN
    Result

[mohtism]

@Greg_Deckler @johnt75 

[Pragati11]

Hi @mohtism ,

Did you try creating a relationship between your main table and this Date table?

Can you share more on if you have a data model?

[mohtism]

hey @Pragati11  date will be provided from a manual table and it has no relation with main table.Beside this i still did this and its not working