Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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

Need Help

2 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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!