Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

PowerBI Measure

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

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    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.

     

  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

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

     

4 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    7 years ago

    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.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hello ,

      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

       

       

      • v-frfei-msft's avatar
        v-frfei-msft
        Community Support
        7 years ago

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