Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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
Solved! Go to 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])
@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")
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])
User | Count |
---|---|
85 | |
79 | |
64 | |
52 | |
46 |
User | Count |
---|---|
101 | |
49 | |
42 | |
39 | |
38 |