PowerBI Measure

[Anonymous]

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

[v-frfei-msft · Accepted 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.

[v-frfei-msft · Accepted 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.

[Anonymous]

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

[v-frfei-msft · Accepted solution]

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

[Anonymous]

Hi v-frfei-msft,

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