QTD as of previous month

[Mass]

I want to display QTD as of previous month means In june, the value should be only for Apr and May. Can anyone give me the formula. The below one is giving QTD not as of previous month

QTD PrevMonth =
CALCULATE (
    [Total Production],
    FILTER (
        ALL ( 'Months'[Month] ),
        'Months'[Month] <= MAX(Months[Month]) )
    )

[v-yuezhe-msft · Accepted solution]

@Mass,

In your scenario, firstly, create a Year table listing years(2012,2013,2014,2015,2016,2017,2018…), create a Month Table listing months(1,2,3…12), but don’t create any relationship between the Year/Month table and your original table.

Secondly, in your original table(take Sheet4 table for example), create a year column and month column using following DAX.

Year = YEAR(Sheet4[DateKey])
Month = MONTH(Sheet4[DateKey])

Then create a measure in your original table using DAX below.

CheckMeasure = IF(MAX(Sheet4[year])=MAX(Year[Year])&& MAX(Sheet4[Month])<MAX(Month[Month])&&MAX(Sheet4[Month])>=MAX(Month[Month])-2,1,0)

Thirdly, create a slicer using your Year table, create a slicer using your Month table and drag the CheckMeasure to the Visual level filters of your visual and filter the measure to 1 only. This way, when you select year and month in the slicers, the visual will show value for previous two months value. You can review the following example for more details.

Regards,

Lydia