Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Frequent Visitor

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



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

Community Support
Community Support

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.

    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"})

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.


Helpful resources

Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City


Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.