Forum Discussion
MDX to DAX
Super UserAnonymous , Can you tell us what these are doing , might be easy to suggest
also, check
https://www.sqlbi.com/articles/converting-mdx-to-dax-first-steps/
HI Amith,
These are calculated mesures in my SSAS multildimensinal model. We are planning to migrate the same to PowerBI reports.
- amitchandak
Anonymous , I am not an expert at MDX, so wanted what you need, so I can help.
Like we have functions datesmtd, Datesqtd, and datesytd for till date.
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
But we can combine them with slicer selection
Switch Period =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min =
SWITCH(SELECTEDVALUE(Period[PeriodType])
"YTD",if( Month(_max) <4 , date(year(_max)-1,4,1) ,date(year(_max),4,1)), //FY April -March
"QTD",eomonth(_max,-1* if( mod(Month(_max),3) =0,3,Month(_max)))+1,
"MTD",eomonth(_max,-1)+1 ,
BLANK())
return
CALCULATE(Sum(Sales[Sales[Amount]),DATESBETWEEN('Date'[Date],_min,_max))Hi Amit,
Thank you so much for your response. Please find below details of mdx query details.
Measure = SUM(PeriodsToDate([Time_Period].[Time_Period].[Period],[Time_Period].[Time_Period].CurrentMember),[Measures].[Hrs_Act_Admin_POS])
Above MDX query is to create calculated measure for belwo purpose.
to navigate between same level members. It returns the set of sibling members from First Sibling to the specified member. For example, If you want to list the members from one position to a particular member, then we can use this MDX PeriodsToDate function.
for more information about PeriodToDate https://www.tutorialgateway.org/mdx-periodstodate-function/