converting DAX formula into M

[Etienne_1995]

Hi,

So this is my first post in this kind of community, since I'm really stuck. I'm quit new to Power Query Editor and need to convert two measures. 😅

The goal is to create a new column in the query editor and use it as a filter for the whole report. First I need two columns for the average price of the current year and the past year: for example here is the DAX-formular for the current year.

average_price_current_year =

VAR price_current_year =

CALCULATE(

SUM(ETL_Savings[ERBP_price_EW]),

FILTER(ETL_Savings, ETL_Savings[ERBK_Date].[YEAR] = YEAR(TODAY()))

)

VAR quantity_current_year =

CALCULATE(

SUM(ETL_Savings[ERBP_quantity]),

FILTER(ETL_Savings, ETL_Savings[ERBK_Date].[YEAR] = YEAR(TODAY()))

)

VAR average_price_current_year = price_current_year / quantity_current_year

RETURN average_price_current_year

and in the next step I would like to add the filter-column with the two newly created columns as source.

filter = IF(NOT(ISBLANK([average_price_current_year])) && NOT(ISBLANK([average_price_last_year])),1,0)

Any help is highly appreciated! 😊

[v-yanjiang-msft · Accepted solution]

Hi @Etienne_1995,

According to your formula, I create a sample and get the result:

If you want to perform the result in M language, here's my solution. Add a custom column.

let 
price_current_year =List.Sum(Table.SelectRows(#"Changed Type",(x)=>Date.Year(x[ERBK_Date])=Date.Year(Date.From(DateTime.LocalNow())))[ERBP_price_EW]),
quantity_current_year =List.Sum(Table.SelectRows(#"Changed Type",(x)=>Date.Year(x[ERBK_Date])=Date.Year(Date.From(DateTime.LocalNow())))[ERBP_quantity])
in 
price_current_year/quantity_current_year

Result:

Here's the whole M syntax, you can copy-paste in a blank query to see the details.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdBLCsMwFEPRvXgcqCV/Yq8lZP/baPvkhlQtxpOTYNA9jsRMPF4nbel9W07ntpShNC2hxbSGVtMW2kx7aDfd/74wQofpDJ2myJqR3dc83wcNxPdCfmqUeOvm6/fqriJo7mqC7q4q2N3VBcNdZTDd1YbZXXUI99WH/PmgxbwWn08=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ERBK_Date = _t, ERBP_quantity = _t, ERBP_price_EW = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ERBK_Date", type date}, {"ERBP_quantity", Int64.Type}, {"ERBP_price_EW", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let price_current_year =List.Sum(Table.SelectRows(#"Changed Type",(x)=>Date.Year(x[ERBK_Date])=Date.Year(Date.From(DateTime.LocalNow())))[ERBP_price_EW]),quantity_current_year =List.Sum(Table.SelectRows(#"Changed Type",(x)=>Date.Year(x[ERBK_Date])=Date.Year(Date.From(DateTime.LocalNow())))[ERBP_quantity])in price_current_year/quantity_current_year)
in
    #"Added Custom"

I also attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[wdx223_Daniel]

NewStep=let a=Table.Buffer(Table.Group(Table.SplitColumn(ETL_Savings,"ERBK_Date",each {_,Date.Year(_)},{""ERBK_Date","Year"}),{"OtherKey1","OtherKey2","Year"},{"AveragePrice",each List.Sum([ERBP_price_EW])/List.Sum([ERBP_quantity])})),b=Date.Year(DateTime.LocalNow()) in Table.FromRecords(Table.TransformRows(ETL_Savings,each _&[AvergePriceCurrentYear=a{[OtherKey1=[OtherKey1],OtherKey2=[OtherKey2],Year=b]}?[AveragePrice]?,AvergePriceLastYear=a{[OtherKey1=[OtherKey1],OtherKey2=[OtherKey2],Year=b-1]}?[AveragePrice]?]))