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

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