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.

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.