Dax needed to calculate the last 12 month completed sales

[Avivek]

Although my requirement is simple but I seem to making some mistake as I am unable to achieve the result.

I have a slicer which shows the month and year and based on the Mon-YY selected I need to get the last 12 months completed sales.

The dax I used is 

Sales amount 12 month =

---------------------------------------------------------

VAR _selectedAltYear =

SELECTEDVALUE( 'Date'[YY_MM])

-----------------------------------------------------

//new date table

VAR __rlvntDateTable =

CALCULATETABLE(

'Date',

FILTER('Date','Date'[YY_MM]>= edate([From Max Date],-12) && 'Date'[Date]<= [Max Date of To Year])

)

---------------------------------------------------------

VAR _altsalesamount =

CALCULATE( [Sales Amount], __rlvntDateTable )

RETURN

_altsalesamount

But this measure does not seem to work for me.  Can anyone help me with the given measure

[v-angzheng-msft · Accepted solution]

Hi, @Avivek

Try this:

var _maxDate=CALCULATE(MAX('Table'[Date]),ALLSELECTED('Table'))
VAR __rlvntDateTable =
FILTER(ALL('Table'),EOMONTH('Table'[Date],0)<=EOMONTH(_maxDate,0)&&EOMONTH('Table'[Date],0)>EOMONTH(_maxDate,-12))

Sample:

Measure:

Measure = 
var _maxDate=CALCULATE(MAX('Table'[Date]),ALLSELECTED('Table'))
var _result=
CALCULATE(
    SUM('Table'[Value]),
    FILTER(ALL('Table'),EOMONTH('Table'[Date],0)<=EOMONTH(_maxDate,0)&&EOMONTH('Table'[Date],0)>EOMONTH(_maxDate,-12))
    )

return _result

Please refer to the attachment below for details. Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

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

[parry2k]

@Avivek try changing this:

VAR __rlvntDateTable =

FILTER(ALL('Date'),'Date'[YY_MM]>= edate([From Max Date],-12) && 'Date'[Date]<= [Max Date of To Year])

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.