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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

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

 

 

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors