Forum Discussion

Etienne_1995's avatar
Etienne_1995
Frequent Visitor
3 years ago
Solved

converting DAX formula into M

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! ðŸ˜Š
Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    3 years ago

    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.

2 Replies

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    3 years ago

    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's avatar
    wdx223_Daniel
    Community Champion
    3 years ago

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