Reply
Frequent Visitor
Posts: 4
Registered: ‎12-12-2018
Accepted Solution

Migrating MDX code to DAX: lag functions and tuples

 

I have to port some MDX formulas to DAX, in order to move from Pentaho to Power BI. I have been all day trying to change these formulas but I am not getting any progress, my main problems now are with these two formulas:

%succes=Avg([Fecha Oferta.Mes].CurrentMember.Lag(11):[Fecha Oferta.Mes].CurrentMember,[Measures].[Importe Neto Factura]/[Measures].[Importe Neto Oferta])


futureBenefits=[Measures].[Net Offer quantity]*Avg([Date Offer.Month].CurrentMember.Lag(11):[Date offer.month].CurrentMember,[Measures].[% Exito de Of en Fras (Importe)])

After that I have to display them in a matrix, separated by years and months I have been trying to use things like this:

AVERAGEX(FILTER(offers;DATEADD(dim_time[date];-1;YEAR));SUM(offers[net_quantity_ticket])/SUM(offers[net_quantity_offer]))

But it is not working, I just get the same results that I get using this:

AVERAGEX(offers;SUM(offers[net_quantity_ticket])/SUM(offers[net_quantity_offer]))

I have also tried using CALCULATE instead of AVERAGEX +FILTER but it does not even get accepted because average needs a column, not a measure. Any suggestions about how could I translate this code? Sorry but I cannot post any data since it is confidential
Thanks a lot for your help


Accepted Solutions
Community Support Team
Posts: 4,002
Registered: ‎07-09-2016

Re: Migrating MDX code to DAX: lag functions and tuples

@dcruzado,

 

You may view the code generated by Quick measure Rolling average.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post


All Replies
Community Support Team
Posts: 4,002
Registered: ‎07-09-2016

Re: Migrating MDX code to DAX: lag functions and tuples

@dcruzado,

 

You may view the code generated by Quick measure Rolling average.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Frequent Visitor
Posts: 4
Registered: ‎12-12-2018

Re: Migrating MDX code to DAX: lag functions and tuples

[ Edited ]

Thanks, that was what I needed, I ended up with code looking like this:

 
% success =
IF(
    ISFILTERED('dim_time'[date]);
    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column.");
    VAR __LAST_DATE = ENDOFMONTH('dim_time'[date].[Date])
    VAR __DATE_PERIOD =
        DATESBETWEEN(
            'dim_time'[date].[Date];
            STARTOFMONTH(DATEADD(__LAST_DATE; -11; MONTH));
            __LAST_DATE
        )
    RETURN
        AVERAGEX(
            CALCULATETABLE(
                SUMMARIZE(
                    VALUES('dim_time');
                    'dim_time'[date].[Year];
                    'dim_time'[date].[QuarterNo];
                    'dim_time'[date].[Quarter];
                    'dim_time'[date].[MonthNo];
                    'dim_time'[date].[Month]
                );
                __DATE_PERIOD
            );
            CALCULATE([% success(Importe)]; ALL('dim_time'[date].[Day]))
        )
)
 
Thanks again!