DAX How to express today "one month ago"

3
votes

I want to create a card with last month's sales using Power BI Desktop.

For that, I need a measure that is capable of expressing today "one month ago"

For example, this month's sales is:

This month sales =
VAR ThisMonth =
    MONTH ( TODAY () )
RETURN
    CALCULATE (
        'orders'[SalesAmount];
        'calendar'[month_number] = ThisMonth;
        'calendar'[year] = 2017
    )

All time intelligence functions seem to be good to handle columns of date but not scalar values like this case is.

Also, because I'm using a card, there's no "filter context", therefore, I need a volatile function like TODAY.

Thanks!

2
powerbidax
Can you not just add a column to the calendar table that is 1 for last month and 0 otherwise....then just use that in a calculate function as one of the filter context adjusters – whytheq

2 Answers

6
votes

Can you just do 

LastMonth = MONTH(EOMONTH(TODAY(),-1))

and use that instead of ThisMonth in your formula?

1
votes

TYLM Value = CALCULATE([Total Value],DATESMTD(DATEADD('CALENDAR'[DATE],-1,MONTH)))

if you have a calendar table the above should work for you. You can limit the calendar table to Today as the latest date. the above measure will then work as you need.