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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Cango
Frequent Visitor

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

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 

Cango_2-1700481155640.png

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

 

Thank you

1 REPLY 1
v-junyant-msft
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.

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.

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

AugPowerBI_Carousel

Power BI Monthly Update - August 2024

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

August Carousel

Fabric Community Update - August 2024

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