How Can I convert this so I can use it in slicers/filters

[Cango]

Hello,

Can I build this Code in Power Query Editor ? 

How Do I have to change this:

Previous Profit = 
CALCULATE ( SUM ( 'Table'[Profit] ),
    FILTER ( ALLEXCEPT('Table','Table'[Shop]),
        YEAR ( [Date] ) = EARLIER ( 'Table'[Date].[Year] ) - 1
            && MONTH ( [Date] ) = EARLIER ( 'Table'[Date].[MonthNo] )
    )
)

to implement it here 

The reason why i wanna convert this is, because I can´t use it in slicers.

Thank you

[v-junyant-msft]

Hi @Cango ,

Since you didn't provide a pbix file, I'm not sure if the M code I provided is valid, so please modify it according to your own situation:

//Import a data source. Suppose you already have a table with years, months, and profits.

let
    Source = Excel.CurrentWorkbook()
{[Name = "table"]}[Content],
    ChangedType = 
Table.TransformColumnTypes(Source,{{"Year",Int64.Type},{"Month",type text},{"Profit", Decimal.Type}}),

//Add the "Previous Year" column.

    AddedPreviousYear =
Table.AddColumn(ChangedType,"Previous Year",each[Year]-1),

//Join the original table to the table with "Previous Year" added to it, matching the year and month.

    JoinedTables =
Table.NestedJoin(AddedPreviousYear,{"Previous Year","Month"},ChangedType,{"Year","Month"},"Previous Profit",JoinKind.LeftOuter),

//Expand the new table to show the profit for the same month of the previous year.

    ExpandedPreviousProfit =
Table.ExpandTableColumn(JoinedTables,"Previous Profit",{"Profit"})
in
    ExpandedPreviousProfit

The DAX code you've provided calculates the profit for the previous year while keeping the same month. To replicate this in Power Query, I can offer you another solution:
In the Power Query, go to the "Add Column" tab and use the "Date.Year" and "Date.Month" functions to extract the year and month from your date column.
Right-click on your table in the Queries pane and select "Duplicate".
In the duplicated table, create a new custom column that subtracts 1 from the year column to represent the previous year.
Go back to the original table, go to the "Home" tab, and click on "Merge Queries".
Select the adjusted table and perform an inner join on the Shop, Year (minus 1), and Month columns.
Once merged, click on the expand button next to the new column created by the merge and select the Profit column.
Rename the expanded Profit column to "Previous Profit" and perform any additional calculations if necessary.
Once you have your "Previous Profit" column, go to the "Home" tab and click "Close & Apply" to load the data back into Power BI.

Either way, since you didn't provide a pbix file or sample data, I can't guarantee that it will work correctly with you directly, and ask you to adjust the steps to your own situation please.

Best Regards,

Dino Tao

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