Latest Month,Qtr and Year

[Anonymous]

I have a table with 3 years  Data(2018,2019,2020-January month only) and am writing a dax to get the latest month,QTR and year available in the  data . If i select monthly filter i need latest month data .I tried but am not able to get the correct output.

Requirement- Need latest month , Qtr and Year from date data

1.Latest month=(Month-2020 january is the latest month)

2.latest qtr = janauary 2020 and nov,Dec 2019 data

3.latest Year=january 2019 to january 2020

[amitchandak]

@Anonymous , Assume you have Date table with Month , Year and Qtr

New column in date table

Latest Month=

var _date = max(Table[Date])

return

if(eomonth([Date],0) =eomonth(_date,0), "Latest Month", format([Date], "mmm-yyyy")

Latest year=

var _date = max(Table[Date])

return

if(year([Date]) =year(_date), "Latest Year", format([Date], "yyyy")

Latest qtr=

var _date = max(Table[Date])

return

if(_date>=[Start of qtr] && _date<=[end of Qtr] , "Latest Qtr", format([Date], "\Qq-yyyy")

[Anonymous]

Am trying to get the yearly data its coming only latest year only but i need current month+ previous 11 month data

And the latest month and Qtr i am not getting the expexted output its show full qtr and month

[amitchandak · Accepted solution]

@Anonymous . Try a measure like

Latest 12 Months=

var _max= maxX(allselected(Table) , Table[Date])

Var _min = eomonth(_max, -12)+1

return

calculate(Sum(Table[Value]), filter(Table, Table[date]>=_min && Table[date]<= _max) )

or with date table

Latest 12 Months=

var _max= maxX(allselected(Table) , Table[Date])

Var _min = eomonth(_max, -12)+1

return

calculate(Sum(Table[Value]), filter('Date', 'Date'[date]>=_min && 'Date'[date]<= _max) )

atest 12 Months=

var _max= maxX(allselected(Table) , Table[Date])

Var _min = eomonth(_max, -12)+1

return

calculate(Sum(Table[Value]), filter(all('Date'), 'Date'[date]>=_min && 'Date'[date]<= _max) )

Refer this. You today() is maxX(allselected(Table) , Table[Date])

https://medium.com/chandakamit/cheat-sheet-power-bi-time-intelligence-formulas-using-today-654f26e27304