Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

MDX to DAX

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

6 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      HI Amith,

       

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

      • amitchandak's avatar
        amitchandak
        Super User
        4 years ago

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