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.
I have a query that pulls CurrentDay, PrevDay, PrevMonth, PrevMonthEnd, CurrentValue,PrevDayValue and PrevMonthValue. And i am trying to create a measure called valuechange where the value = CurrentValue-PrevDayValue if date selected in the slicer is in CurrentDay column, or value=CurrentValue-PrevMonthEnd if date is in PrevMonth column. Is there a way to do this in powerbi? I am new and still learning... i appreciate all your help. Thank you
Solved! Go to Solution.
Hi @Anonymous ,
Could you please share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.
Hi @Anonymous ,
One sample for your reference.
1. Unpivot the table in power query. M code for your reference.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSipKzEvOUDBU0lEyMjC0NDA1soAxTYwNgExDAxBpCiKA/FgduBYjnFqMwFrMTcG6lWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [branch = _t, currentday = _t, PremontEnd = _t, Currecntvalue = _t, PrevDayValue = _t, PremnthValue = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"branch", type text}, {"currentday", Int64.Type}, {"PremontEnd", Int64.Type}, {"Currecntvalue", Int64.Type}, {"PrevDayValue", Int64.Type}, {"PremnthValue", Int64.Type}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"PrevDayValue", "PremnthValue", "Currecntvalue", "branch"}, "Attribute", "Value") in #"Unpivoted Other Columns"
2. Close and apply, after that, create a measure as below.
Measure = VAR a = FILTER ( Table1, Table1[Attribute] = "currentday" ) VAR b = FILTER ( 'Table1', Table1[Attribute] = "PremontEnd" ) RETURN IF ( MAX ( Table1[Attribute] ) = "currentday", CALCULATE ( SUM ( Table1[Currecntvalue] ) - SUM ( 'Table1'[PrevDayValue] ), KEEPFILTERS ( a ) ), CALCULATE ( SUM ( Table1[Currecntvalue] ) - SUM ( Table1[PremnthValue] ), KEEPFILTERS ( b ) ) )
Hi @Anonymous ,
Could you please share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.
Hello v-frfei-msft,
Thank you for the reply. I have uploaded a PIBX file with my sample data. I converted it to an excel file, but my data would be either a direct query or import from a SQL table.
https://1drv.ms/u/s!AgMNBTdCmoy4jY1xpjrDBUV3PfGkzw?e=jEG8Lg
In the test report, i have a matrix, and i was trying to make it look like the screenshot below.
With Change measure calculated as CurrentValue-PrevDayValue if Date is CurrentDay and CurrentValue-PrevMnthValue if Date is PrevMonthEnd.
Thank you again
Hi @Anonymous ,
One sample for your reference.
1. Unpivot the table in power query. M code for your reference.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSipKzEvOUDBU0lEyMjC0NDA1soAxTYwNgExDAxBpCiKA/FgduBYjnFqMwFrMTcG6lWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [branch = _t, currentday = _t, PremontEnd = _t, Currecntvalue = _t, PrevDayValue = _t, PremnthValue = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"branch", type text}, {"currentday", Int64.Type}, {"PremontEnd", Int64.Type}, {"Currecntvalue", Int64.Type}, {"PrevDayValue", Int64.Type}, {"PremnthValue", Int64.Type}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"PrevDayValue", "PremnthValue", "Currecntvalue", "branch"}, "Attribute", "Value") in #"Unpivoted Other Columns"
2. Close and apply, after that, create a measure as below.
Measure = VAR a = FILTER ( Table1, Table1[Attribute] = "currentday" ) VAR b = FILTER ( 'Table1', Table1[Attribute] = "PremontEnd" ) RETURN IF ( MAX ( Table1[Attribute] ) = "currentday", CALCULATE ( SUM ( Table1[Currecntvalue] ) - SUM ( 'Table1'[PrevDayValue] ), KEEPFILTERS ( a ) ), CALCULATE ( SUM ( Table1[Currecntvalue] ) - SUM ( Table1[PremnthValue] ), KEEPFILTERS ( b ) ) )
I really appreciate your help. I am currently out of the office this week. But i will try your suggestion when i get back to the office and let you know.
Thank you again.
Glen
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |