Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
Anonymous
Not applicable

Latest Month,Qtr and Year

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

1 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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@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")

 

 

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

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

@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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

May 2025 Monthly Update

Fabric Community Update - May 2025

Find out what's new and trending in the Fabric community.