Filter dates in a table using a measure (because creating a new column/table is not an option)

[ariana_night]

Hi there,

I have a list of book titles that have a wide range of onsale dates (ex: from 1975 til today). Creating a measure is the only option for me with this specific dashboard (I am not allowed to create a new column or table).

What I need to do: In my POS table visual, I need to only show the data for titles that have an "onsale date" at least 1 month old. I need to use the measure as a "filter on this visual" or "on this page". 

What I have attempted: A LOT of different things. But in a nut shell, I've tried a variety of functions where I am telling the measure to look at the Onsale Date column in the Product Hierarchy Table and returning the Onsale dates that have a Start Date of 1 month before today. Since I need this automated when the data refreshes. 

Relationships: The Product Hierarchy table is connected to all of the POS data and many other data. 

My experience: I have only been working with PowerBI for 1 month. So I am not well versed in DAX.

I look forward to any and all feedback. I have done a lot of research on line and in this forum and nothing has worked for me since. 

[sturlaws · Accepted solution]

Hi, @ariana_night,

based on the information you have provided, I have created a small sample report:

with the measure looking like this:

Last onsale date older than 1 month =
VAR _tmp =
    FILTER (
        'Table',
        'Table'[Onsale date]
            <= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, DAY ( TODAY () ) )
    )
RETURN
    IF ( COUNTROWS ( _tmp ) >= 1, 1, 0 )

cheers,

Sturla

[ariana_night]

This worked! Thank you so much! You are a lifesaver 😁