Date filter in SUM

[MIKAOKSA]

I need to add date filter to sum calculation. My current formula is this:

AMOUNT_STOCK divided by AMOUNT_LOC_NOTAX =

DIVIDE(

    SUM('MET_PURCHASE'[AMOUNT_STOCK]),

    SUM('INV_HEADER'[AMOUNT_LOC_NOTAX])

I need AMOUNT_STOCK filtered last calendar month and AMOUNT_LOC_NOTAX filtered last 12 calendar months. How to do this?

Appreciate if someone would help.

BR, 

Mika

[Tahreem24 · Accepted solution]

@MIKAOKSA you could try something like this:

 =
VAR LastMonth_AmtStock = CALCULATE(SUM('MET_PURCHASE'[AMOUNT_STOCK]),PREVIOUSMONTH(DateTable[DateColumn]))
VAR LastYear_AmtStock = CALCULATE(SUM('INV_HEADER'[AMOUNT_LOC_NOTAX]),DATEADD(DateTable[DateColumn],-12,MONTH))

RETURN DIVIDE(LastMonth_AmtStock,LastYear_AmtStock)

[MIKAOKSA]

This worked like a charm, thank you!

[Tahreem24]

@MIKAOKSA Awesome. Good to know.

[manikumar34]

@MIKAOKSA , 

Use DATESINPERIOD or DATESBETWEEN functions to achieve this.

[amitchandak]

@MIKAOKSA , You can try a measure with date table like

Rolling 12 = CALCULATE([AMOUNT_STOCK divided by AMOUNT_LOC_NOTAX],DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

If you need a trend then refer -https://www.youtube.com/watch?v=duMSovyosXE