cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

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

@Anonymous,

 

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.
2 REPLIES 2
Community Support Team
Community Support Team

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

@Anonymous,

 

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
Anonymous
Not applicable

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

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!

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 197 members 2,112 guests
Please welcome our newest community members: