Forum Discussion
Helper IIIDAX Query for last 24 month using dax studio
Hello Everyone,
Need some help with the DAX query. I copied the DAX from the performance analyzer but need to add a dynamic filter formula can anyone take a look and provide any suggestions? instead of the dates, I need the last 24 months of date listed under VAR __DS0FilterTable2
I tried this
VAR __DS0FilterTable2 =
FILTER(
KEEPFILTERS(VALUES('dimDate'[FullDate])),
AND('dimDate'[FullDate] >= DATEADD('dimDate'[FullDate],-24,Month), 'dimDate'[FullDate] < DATEADD('dimDate'[FullDate],0,Month))
it is giving blank value
// DAX Query
DEFINE
VAR __DS0FilterTable =
TREATAS({"Y"}, 'dimDOTCarry'[IsActive])
VAR __DS0FilterTable2 =
FILTER(
KEEPFILTERS(VALUES('dimDate'[FullDate])),
AND('dimDate'[FullDate] >= DATE(2021, 7, 21), 'dimDate'[FullDate] < DATE(2023, 7, 21))
)
VAR __DS0FilterTable3 =
TREATAS({4}, 'dimDOTCarrierInfo'[FMSID])
VAR __DS0Core =
SUMMARIZECOLUMNS(
'dimCrash'[Crash Date],
'dimCrash'[Report Number],
'dimDOTCarry'[Number],
'dimDOTCarry'[Name],
Thank you so much for the time.
2 Replies
- amitchandak
Super User
Nepal101 , Last 24 month based on today
24 month Today =
var _min = date(year(today()), month(today())-24,day(today()) )
var _max = today()
return
CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))All About Time Intelligence around Today: https://youtu.be/gcLhhxhXKEI
If you want select date and want aggregated 24 months values
Rolling 12 Sales =
var _max = maxx(allselected(date),date[date]) // or today()
var _min = date(year(_max), month(_max)-12,1)
return
CALCULATE(SUM(Sales[Sales Amount]),filter(all(date), date[date] <=_max && date[date] >=_min))If you need trend then you need slicer on independent table
//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = date(year(_max), month(_max)-24, Day(_max))
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI
Nepal101Thank you for your reply,
I was wondering if it is possible to just calculate the filter for the date rather than using it for any fact field like calculate ( sum(Table[Value]).
Please let me know if there is any way to do it
