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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. 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

Share with Power BI Enthusiasts: 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")

 

 

Share with Power BI Enthusiasts: 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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

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.

June 2025 community update carousel

Fabric Community Update - June 2025

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