Forum Discussion
PowerBI Measure
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 ) ) )