DAX to show Last 12 Months Revenue only up to the previous month

[Anonymous]

Hi All,
I have a requirement to create a DAX calculation of the Last 12 Months' ARR.

For E.g. For the month of June-2021, a report with a Rolling Year would report on data from June 2020 - May 2021 which I am able to achieve using below DAX and output as shown in the screenshot.

DAX Formula:

Last 12 Months ARR =

VAR EndDate = EOMONTH(MIN('Transaction Date'[Date Key]),-1)

RETURN

CALCULATE (

SUM('Transaction'[Sales Amount]),

FILTER( ALL('Transaction Date'),

AND(

'Transaction Date'[Date Key] <= EndDate ,

DATEADD('Transaction Date'[Date Key],12,Month) > EndDate )))

Now, what I want is to show the Last 12 Months' ARR only up to the end of the previous month.

As we should not report on an incomplete month so, like in the above example, as we are still in the month of July 2021, we would not include July 2021 data.

Can you please help me in restricting data and the DAX formula should show only till the end of the Previous month and should not show values from July 2021 onwards?

[v-kelly-msft · Accepted solution]

Hi  @Anonymous ,

Modify your measure as below:

Last 12 Months ARR = 
VAR EndDate = EOMONTH(MIN('Transaction Date'[Date Key]),-1)
var enddate2=EOMONTH(TODAY(),-1)
RETURN
IF(MAX('Transaction Date'[Date Key])<=enddate2,
CALCULATE (
SUM('Transaction'[Total Revenue]),
FILTER( ALL('Transaction'),
AND(
'Transaction'[Transaction Date Key] <= EndDate ,
'Transaction'[Transaction Date Key]>=MINX(DATEADD('Transaction Date'[Date Key],-12,Month),'Transaction Date'[Date Key])  ))))

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

Hi @v-kelly-msft,
Thanks for replying and thank you for your efforts, this has solved my requirement.

[Anonymous]

Hi @Ashish_Mathur , @parry2k 
Here is the link to the sample data.
https://drive.google.com/file/d/1mXThN2dBPnUV5hjrjK0OYk_als_Pj1P6/view?usp=sharing
Please let me know if anything is not clear or if you need more information.
Thanks in Advance!

[Ashish_Mathur]

Hi,

I believe @v-kelly-msft has already answered your question.

[Anonymous]

Hi, @Ashish_Mathur,
Yes, @v-kelly-msft has answered my question.
Thank you for following up 👍

[parry2k]

@Anonymous I'm still not clear what you are actually looking for. It is always a good idea to put a sample data file and explain the expected output. You know your requirement and data better than anyone else, better you explain, quicker the solution you get otherwise we can keep going in circles. cheers!!

[parry2k]

@Anonymous for any time intelligence, it is a best practice to have a calendar table in your model, you can add easily by following my blog post here Create a basic Date table in your data model for Time Intelligence calculations | PeryTUS IT Solutions

Once you have a table relationship with the calendar table, add the following DAX measure, replace the table and column name as per your data model:

Last 12 Months Upto Previous Month = 
VAR __endMonth = EOMONTH ( TODAY(), -1 )
RETURN
CALCULATE ( 
    SUM ( Sales[Sales] ),
    KEEPFILTERS (
        DATESINPERIOD (
            'Calendar'[Date],
            __endMonth,
            -12,
            MONTH
        )
    )
)

To visualize, use month from calendar table and above measure, and everything should work as expected.

✨ Follow us on LinkedIn

Check 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.⚡

[Anonymous]

Hi @parry2k,
Thank you for your answer,
here are my thoughts on this:
1) I do have a Transaction Date table which is similar to the Calendar table just that it's not created in PowerBI rather in the backend. But it has the Latest Transaction Date as of 12/31/2021 because of which I get July 2021 onwards data as well in the Last 12 Months' ARR DAX calculation.
2) An alternate approach to this is I have tried using date from my Transaction table which has the Latest Transaction Date as of todays' date i.e. 07/25/2021 which still gives me data till July 2021 as shown below.
So, I am not sure creating Calendar Table (especially only for this measure calculation) will help me in my case.

Also, I tried using your given DAX formula, but the result is not correct because we are using the Today() function which will show results only from July-2020 till June-2021 and not for all the months.

[Ashish_Mathur]

Hi,

I'd like to help but am not clear of what you want.  Share the link from where i can download your PBI file and show the expected result clearly.