MDX to DAX

[MMadhusudhan]

I am facing complexity while converting MDX to DAX(SSAS to PowerBI). Can you please help here to convert below calculations

1.

SUM(PeriodsToDate([Time_Period].[Time_Period].[Period],[Time_Period].[Time_Period].CurrentMember),[Measures].[Hrs_Act_Admin_POS])

2.

SUM(([Time_Period].[Time_Period].currentmember.parent.firstsibling.firstchild:[Time_Period].[Time_Period].currentmember.parent.lastsibling.lastchild),
[Measures].[Hrs_LY_Admin_POS])

3. 

SUM(([Time_Period].[Time_Period].currentmember.FirstSibling:[Time_Period].[Time_Period].currentmember.lastsibling),[Measures].[Hrs_LY_POS])

[amitchandak]

@MMadhusudhan , 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/

[MMadhusudhan]

HI Amith,

These are calculated mesures in my SSAS multildimensinal model. We are planning to migrate the same to PowerBI reports. 

[amitchandak]

@MMadhusudhan , 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))

[MMadhusudhan]

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/

[v-yiruan-msft]

Hi @MMadhusudhan ,

In order to be able to provide you with a suitable solution more quickly, could you please provide some simplified sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

How to upload PBI in Community

Best Regards

[MMadhusudhan]

@amitchandak 

Please let me know if there is any suggesions.