Previous 6 months Data

[Anonymous]

Hi all,
Need your assistance to find a formula to write the DAX for previous 6 months.
Between Aug 2020 to Dec 2020, there is no value.

Below screenshot of desired ouput

Regards
Hidayat

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

Create a Yearmonth column from the date column.

year_month = YEAR('table'[date])*100+MONTH('table'[date])

Create index columns based on month and year.

month_index = 
VAR MonthRow = 'table'[year_month]
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'table'[year_month] ),
        FILTER ('table','table'[year_month] <= MonthRow )
    )

Create an unrelated calendar table as slicer.

Table 2 = SELECTCOLUMNS('table',"DateKey",'table'[date],"YearMonth",'table'[year_month])

Create a measure and apply it to a visual level filter.

Last6Months = 
//Get the year_month from the slicer
var max_date = SELECTEDVALUE('Table 2'[YearMonth])
//Get the index of year_month
var _mindex = CALCULATE(MAX('table'[month_index]),FILTER(ALL('table'),'table'[year_month]=max_date))
//Get the min year_month of the last six months
var min_date = CALCULATE(MIN('table'[year_month]),FILTER(ALL('table'),'table'[month_index]=_mindex-5))
return IF(MAX('table'[year_month])>=min_date&&MAX('table'[year_month])<=max_date,1,0)

Sample .pbix

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

Try like with date table

Rolling 6 = CALCULATE(Average(Table[Trade Price]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-6,MONTH))

[Anonymous]

Hi @amitchandak ,

Not looking for average in the statement.
I would need the statement to lookup the last 6 month record.

Regards
Hidayat