Query

[Vijen_M]

How should i get MTD,LMTD,YTD,Last12 month, i have a date column in which date are from year 2019 to year 2022 till 22 nov

[vinieme12 · Accepted solution]

MTD

sum({<Datefield = {">=$(=Date(Monthstart(Max(Datefield ))))<=$(=Date(Max(Datefield )))"}>} SomeMeasure)

LMTD

sum({<Datefield = {">=$(=Date(Monthstart(Max(Datefield ),-1)))<=$(=Date(AddMonths(Max(Datefield ),-1)))"}>} SomeMeasure)

YTD

sum({<Datefield = {">=$(=Date(YearStart(Max(Datefield ))))<=$(=Date(Max(Datefield )))"}>} SomeMeasure)

Last 12 Months

sum({<Datefield = {">=$(=Date(Monthstart(Max(Datefield ),-12)))<=$(=Date(Max(Datefield )))"}>} SomeMeasure)

replace Datefield with your actual date field name