Create measure for same column

[brandonchan]

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

[Icey]

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

[Icey · Accepted solution]

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]

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