Forum Discussion
MIKAOKSA
Helper I
Helper IDate filter in SUM
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
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)
5 Replies
Tahreem24Super User
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)- 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
- manikumar34
Solution Sage
