Dynamic filters - Selected Value - DAX

[brunofs123]

Hello!

I have two tables, CALENDAR TABLE and the Fact Table.

In Fact Table I have TWO date columns, let’s call FT[date1] and FT[date2].

Both tables are connected. Calendar[Date] à FT[date1]  (picture).

My main measure is  simple:

volume m3s  = SUM('Outorgas validas  2'[Volume m3s])

There is a “catch”. Speaking of 2018, I can only consider the values which are:

FT[date1]  >= 01/01/2018   (DD, MM, YYYY)

FT[date2] <=31/12/2017

To make it clear, date1 is something like “expiring date”; and date 2 is “publishing date”.

What I want:

A dynamic FILTER, with the Calendar[Date] in a slicer. When I choose any date or interval, it dynamically apply the filters to calculate, calculating within the conditions. Let’s say I chose “15/01/2007”:

FT[date1]  >= 15/01/2007   (DD, MM, YYYY)

FT[date2] <=14/01/2007

What I have thought:

Result =

VAR SELECTED = SELECTEDVALUE(dCalendar[Date])

RETURN

CALCULATE([volume m3s];

    FILTER('Outorgas validas  2';

            FT[date1]  >= 15/01/2007   >= SELECTED &&

             FT[date2] <=14/01/2007  <= SELECTED)

***

But I wasn’t able to get the correct results…

Thanks in advance!

(english is not my native language)

[v-frfei-msft]

Hi @brunofs123,

Here we can create another calculated table and add the date column of the new table as slicer.

_New = Values(Calendar[Date])

And update your measure as below.

Result =

VAR SELECTED = SELECTEDVALUE(_New[Date])

RETURN

CALCULATE([volume m3s];

FILTER('Outorgas validas 2';

FT[date1] >= 15/01/2007 >= SELECTED &&

FT[date2] <=14/01/2007 <= SELECTED)

If it doesn't meet your requirement, kindly share your sample data and excepted result to me. Please upload your files to One Drive and share the link here.

Regards,

Frank

[brunofs123]

Thank you for replying. I was only able to see it today, but unfortunately it didn't work.

Here is lhe one drive link:

https://onedrive.live.com/?authkey=%21ALpvuHsF7hA32hU&cid=60ABE94B81C766DB&id=60ABE94B81C766DB%21138...

I'm afraid i post a wrong calculation in my orginal message:

esult =

VAR SELECTED = SELECTEDVALUE(dCalendar[Date])

RETURN

CALCULATE([volume m3s];

    FILTER('Outorgas validas  2';

            FT[date1]  >= 15/01/2007   >= SELECTED &&

             FT[date2] <=14/01/2007  <= SELECTED)

please desconsider the red lighted characters

thanks!

[v-frfei-msft · Accepted solution]

Hi @brunofs123,

To create a new calculated table and do not create relationship for the new table with other ones.

_newCalendar = CALCULATETABLE('dCalendar')

Then we can add the date column in the new table in slicer and create a measure as below.

out valid DIN 2 = 
VAR SELECTED = SELECTEDVALUE('_newCalendar'[Date])

RETURN 
 CALCULATE(SUM('FT - Outorgas validas  2'[Volume m3s]),
    FILTER('FT - Outorgas validas  2' ,  
    'FT - Outorgas validas  2'[date venc] >= SELECTED &&
    'FT - Outorgas validas  2'[date public] <= SELECTED)
          )

Please refer to the pbix.

Regards,

Frank

[brunofs123]

Thank you!

Mind explaining why you had to create a new and unrelated Calendar?

Thanks..