Last twelve months including this one

[LamdaLamda]

HI All,

I am trying to create a measure in dax that will allow me to get the last 12 months worth of Sales data including this one.  I have seen calcs that discard this month and take the previous 12 months but I want to include it.  So ideally, I would have using today's date Feb 2020 to Feb 2019.  Obviously this month would be a lighter number but it would grow each day obviously until the end of the month.  My org has a date to month to date calc they use that I was hoping to utilize that looks like this:

MTD = IF(
        (MONTH('Date'[Date]) = MONTH(TODAY())

        &&  DAY('Date'[Date]) <= DAY(TODAY()))

,   "MTD"

,   "Non MTD")

I'd love to just be able to say dates between this and this -12 but I know that won't work.  Anyone have any handy calcs for getting the last 12 months including this one?

Thanks!!!

Lamda

[v-yiruan-msft]

Hi @LamdaLamda ,

Whether your problem has been resolved?

Best Regards

Rena

[amitchandak]

Try like

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))  

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Date[Date]),-12,MONTH))  

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin

[LamdaLamda]

Thanks - I tried this one:

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Date[Date]),-12,MONTH)) 

And it works and it doesn't.  The issue is I cannot put it into a card to create on overall metric - it shows up blank in the card.  The second is when I put it into a date range it shows more than the 12 months we were trying to narrow it down to.  Instead of showing 12 months it shows the entire date range my data has.  I need to tweak it somehow.

[v-yiruan-msft]

Hi @LamdaLamda,

Could you please provide the data model and some sample data(exclude sensitive data)? It is better if you can provide your PBIX file. I need to check why time intelligence function is not working in your scenario base on your providing information.

Best Regards

Rena

[amitchandak]

If you put it into the cart. For the last twelve months, the date would end the date of the calendar. Do either use relative date filter at the visual level or generate calendar like

date= calendar(min(sales[date]),max(sales[date]))

[Anonymous]

Wouldn't the datesinperiod crop it to just the last 12 months though?  I'm having a similar issue.