Forum Discussion

brandonchan's avatar
brandonchan
Frequent Visitor
6 years ago
Solved

Create measure for same column

Hi,

 

I am a beginner of power query and have imported a query from SQL and create a matrix visuals. In Values, i drag column "Total Cost". In Columns, i drag the column "Date". As my data contains data in 2019 and 2020, so in matrix visuals it shows in 2 columns. How can I create a measure to show the difference and % change of total cost between 2019 and 2020?

 

Name    2019       2020       Total

A            $20         $25         $45

B            $30         $28         $58

C            $40         $35         $75

 

Thanks

Need Help
  • Icey's avatar
    Icey
    6 years ago

    Hi brandonchan ,

     

    Try this:

     

    1. Create a Calendar table.

    Calendar = CALENDAR ( MIN ( 'Table'[Date] ), MAX ( 'Table'[Date] ) )

     

    2. Create relationship.

     

    3. Create Measures.

    Difference = 
VAR Current_ =
    SUM ( 'Table'[Total Cost] )
VAR Previous_ =
    CALCULATE (
        SUM ( 'Table'[Total Cost] ),
        SAMEPERIODLASTYEAR ( 'Calendar'[Date] )
    )
RETURN
    Current_ - Previous_

    % Difference = 
VAR Current_ =
    SUM ( 'Table'[Total Cost] )
VAR Previous_ =
    CALCULATE (
        SUM ( 'Table'[Total Cost] ),
        SAMEPERIODLASTYEAR ( 'Calendar'[Date] )
    )
RETURN
    DIVIDE ( Current_ - Previous_, Previous_ )

     

    4. Create a Matrix visual.

     

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • Icey's avatar
    Icey
    Community Support
    6 years ago

    Hi brandonchan ,

     

    Try this:

     

    1. Create a Calendar table.

    Calendar = CALENDAR ( MIN ( 'Table'[Date] ), MAX ( 'Table'[Date] ) )

     

    2. Create relationship.

     

    3. Create Measures.

    Difference = 
VAR Current_ =
    SUM ( 'Table'[Total Cost] )
VAR Previous_ =
    CALCULATE (
        SUM ( 'Table'[Total Cost] ),
        SAMEPERIODLASTYEAR ( 'Calendar'[Date] )
    )
RETURN
    Current_ - Previous_

    % Difference = 
VAR Current_ =
    SUM ( 'Table'[Total Cost] )
VAR Previous_ =
    CALCULATE (
        SUM ( 'Table'[Total Cost] ),
        SAMEPERIODLASTYEAR ( 'Calendar'[Date] )
    )
RETURN
    DIVIDE ( Current_ - Previous_, Previous_ )

     

    4. Create a Matrix visual.

     

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    If they are a column in your table then you can use

    Diff = [2020]-[2019]

     

    But if they are like row values. Or you dow it by using unpivot

    https://radacad.com/pivot-and-unpivot-with-power-bi

    After that year would one column. create a new year table and do it like this

    this year = 
CALCULATE(SUM(Sales[Sales Amount]),filter(all(Year),Year[Year]=Max(Year[Year])))


last year = 
CALCULATE(SUM(Sales[Sales Amount]),filter(all(Year),Year[Year]=Max(Year[Year])))

     

     

  • Icey's avatar
    Icey
    Community Support
    6 years ago

    Hi brandonchan ,

     

    Is this problem solved?


    If it is solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.


    If not, please let me know.

     


    Best Regards,
    Icey