The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the August 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
79 | |
72 | |
48 | |
39 |
User | Count |
---|---|
138 | |
108 | |
69 | |
64 | |
56 |