How to add WHERE condition for last 12 months in DAX query

[kadingo]

I have the above sample DAX expression to get product sales data. I want to add some sort of a WHERE clause such that only records where the "[Sale-Date]" is in the last 12 months are returned.

I cant seem to figure out how to add that condition, any one please help.

[v-juanli-msft · Accepted solution]

Hi @kadingo 

I see you have got a good solution from another site.

It seems you use DAX query in SQL Server Management Studio or open source tools like DAX studio.

https://docs.microsoft.com/en-us/dax/dax-queries

As tested, in Power BI desktop, EVALUATE() function  doesn't support.

You can use calculatetable or calculate funtion to replace.

If you use Power BI Desktop, want to show table visual which meets your condition, you could create measures as belwo:

One method is:

when you have "sales" column, you can make the table visual to show sales rows which meets the condtion:

Measure =
CALCULATE (
    SUM ( Product_Category[sales] ),
    FILTER (
        Product_Category,
        Product_Category[Acronym] = "Test Product"
            && Product_Category[Sales-Date]
                >= DATE ( YEAR ( TODAY () ) - 1, MONTH ( TODAY () ), DAY ( TODAY () ) )
    )
)

Method two:

If you have no "sales" column, you could add the [measure 2] in the visual level filter of the table visual.

Measure 2 =
IF (
    MAX ( Product_Category[Acronym] ) = "Test Product"
        && MAX ( Product_Category[Sales-Date] )
            >= DATE ( YEAR ( TODAY () ) - 1, MONTH ( TODAY () ), DAY ( TODAY () ) ),
    1,
    0
)

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]

Hi @kadingo,

Before all that expression use calculatetable() and apply the filter as it were a measure.

[Anonymous]

You have taken this question from StackOverflow. Stop spamming everywhere with the same questions.

Regards,

Lewis

[kadingo]

In my opinion, trying in two different technical sites might not necessariy be spamming. Look, finally i have got an answer on StackOverflow. And now am posting the answer here on this thread to help a future reader with a similar problem, how can this be spamming. 

In the past, i have posted a question on both stackoverflow and here, and the solution in some cases has come from here but not on stackoverflow. So i know i have two very good sites with technically talented people but i can not tell in advance where the solution will come from.

Well, you can call it spamming but whats important to me is getting a solution to a problem i have at hand than what some one is thinking. Not every thought is correct.

[Anonymous]

Hi @kadingo ,

Had to try but calculatetable() won't work? Seems like a plausible possibility.

Let me know if it worked.

BR,

DR

[Tahreem24]

@kadingo ,

To calculate last 12 months sales use the below measure:

Last Year sales = CALCULATE(SUM('Table'[SalesColumn]),PREVIOUSYEAR('Table'[DateCOlumn]))

Don't forget to hit Thumbs up and accept this as a solution if you find it helpful! 

[kadingo]

I dont want to calculate the sum, i want to return raws / records for the last 1 year (12 months)