DAX Dynamic measures for use with slicers as a filter

[Anonymous]

This is a problem and I have been mulling over how to do this for the past few days with little to no success.

I am trying to create a calculated table that references measures which themselves reference a 'period' and 'date interval' table such that they can be used as slicers to filter out irrelevant dates from a static 'Date' table.

I was shown this link which parameterises the 'period' and 'date interval' and I created those tables. This is the DAX I used to create them.

CALCULATED TABLES:

Period =  GENERATESERIES(0,365,1)

Date Interval ={( "Day"),("Month"),("Quarter"),("Year")}

MEASURES:

Period1:= MIN('Period'[Period])

Date Range Start1:=
SWITCH (
    TRUE (),
    FIRSTNONBLANK ( 'Date Interval'[DateInterval], TRUE () ) = "Day", FIRSTDATE ( DATEADD ( LASTDATE ( 'Date Range'[Date] ), - ( [Period1] ), DAY ) ),
    FIRSTNONBLANK ( 'Date Interval'[DateInterval], TRUE () ) = "Month", FIRSTDATE (
        DATEADD ( LASTDATE ( 'Date Range'[Date] ), - ( [Period1] ), MONTH )
    ),
    FIRSTNONBLANK ( 'Date Interval'[DateInterval], TRUE () ) = "Quarter", FIRSTDATE (
        DATEADD ( LASTDATE ( 'Date Range'[Date] ), - ( [Period1] ), QUARTER )
    ),
    FIRSTNONBLANK ( 'Date Interval'[DateInterval], TRUE () ) = "Year", FIRSTDATE ( DATEADD ( LASTDATE ( 'Date Range'[Date] ), - ( [Period1] ), YEAR ) )
)

For example, if you wanted a range of 4 days from the LASTDATE() displayed on Power BI, then I would select the number 4 on the period slicer and 'Day' on the Date Interval slicer to achieve this, and every day inbetween will be displayed with all other dates filtered out. I think this is a simple filtering issue but I've been trying this all day and nothing seems to be working. What am I missing or doing wrong?

[v-yuta-msft]

@Anonymous ,

Have you tried the relative filter? In addtion, if you still get stuck on this issue, please share some sample data and clarify more details about your requirement.

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-yuta-msft ,

After talking to others about this, this is the solution that I am trying to implement:

=
CALCULATETABLE (
    'Date',
    FILTER (
        ALL ( 'Date'[Date] ),
        'Date'[Date] >= MAX ( 'Date'[Date Range Start1] )
    )
)

The issue is that the filter condition with the dynamic variable TODAY() works but the dynamic measure ('Date'[Date Range Start 1]) does not.

In other words, the above DAX for the 'Date Range' table doesn't work but the below one does:

=
CALCULATETABLE (
    'Date',
    FILTER (
        ALL ( 'Date'[Date] ),
        'Date'[Date] >= TODAY()
    )
)

The measure 'Date'[Date Range Start1], as defined in the original post, returns the date depending on the date interval and period selected.

Below is a Power BI display of what I am seeing at the moment:

Instead of the date column starting from 01/01/1979, I only want the column to display 13/01/2020 (as per the measure) and all dates onwards to the LASTDATE()

[v-yuta-msft]

@Anonymous ,

So are you trying to achieve the lastdate in the slicer interval?

Regards,

Jimmy Tao