SUM LAST 3 CURRENT MONTS BUT IN PRIOR YEAR

[Hvaldes]

Hello Comunnity

I hope you are fine.

We need your support in order to get a measure of SUM of last 3 months but the previous year.

let me explain now.

I have a measure where I display the sum of last months

Last_3_Months = CALCULATE(SUMX('Facturación MRS',IF(DATEDIFF('Facturación MRS'[DocDueDate],TODAY(),MONTH)<=3 && DATEDIFF('Facturación MRS'[DocDueDate],TODAY(),MONTH) >=1,'Facturación MRS'[Ventas],0)))

'Facturación MRS' : Table where are the total sales

'Facturación MRS'[DocDueDate] : Column in Sales Table where I specify the full date

'Facturación MRS'[Ventas]: Column in Sales Table where I display the sales.

But I need to compare the growth with the last year in the same quantity of months.

For example, if now we are in June, the last measure will be the sum of (May- Abril- March) of 2020

How I can get a the same operation but the previous year May- Abril- March) of 2019?

I have an idea but it doesn't work

YTD LY 3months =
CALCULATE([Last_3_Months],SAMEPERIODLASTYEAR(DimDate[Date]))

I'll appreciate your support and help.

[mahoneypat]

Here is a DAX pattern you can use for this.  Substitute your Table[Column] and the desired aggregation.

Prev 3 Months =
VAR __mindate =
    MIN ( 'Date'[Date] )
VAR __prevdate =
    EDATE ( __mindate, -3 )
RETURN
    CALCULATE (
        SUM(Table[Column]),
        DATESINPERIOD ( 'Date'[Date], __prevdate, 3, MONTH )
    )

You can use the same expression for LY Prev 3 Mos by just replacing -3 to -15 in the EDATE( )

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Hvaldes]

Hi

The dax sentence it does work in a way.

For example here is the example of data in excel for year 2019

If you see the data, in total the number match with Power BI report

but if I put the measure in the table the numbers are not the same.

Other thing that I realized is when I filter just for the months selected, the sentence works.

Do you know what happen this?

Thanks and I'll appreciate your support

[Hvaldes]

Hello

anyone could you help me with this?

Thanks

[lbendlin]

A simple solution would be 

PY Last_3_Months = CALCULATE(SUMX('Facturación MRS',
IF(DATEDIFF('Facturación MRS'[DocDueDate],TODAY(),MONTH)<=15
 && DATEDIFF('Facturación MRS'[DocDueDate],TODAY(),MONTH) >=13,
'Facturación MRS'[Ventas],0)))

but there are lots of questions. 

Why Datediff? Why Today()?  I think DateAdd() or EDATE()  would be better, and DateDim[Date] should replace Today()