Variance from column to column

[Anonymous]

Hello together,

I'm quite new to Power BI. I'm looking for a way to calculate the variance from the values of the current column to the previous one. I have tried variance by category but it wouldn't work. The result would need to look like in the example below:

Date	20.08.2020	21.08.2020	24.08.2020	26.08.2020
Value	500	1000	2000	500
Variance to previous column		500	1000	-1500

Any help is highly appreciated.

Thank you very much!

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

Create an index column in query editor and create a measure like this:

Measure =
VAR last_index =
    MAX ( 'Table (2)'[Index] )
VAR last_value =
    CALCULATE (
        FIRSTNONBLANK ( 'Table (2)'[value], 1 ),
        FILTER ( ALL ( 'Table (2)' ), 'Table (2)'[Index] = last_index - 1 )
    )
RETURN
    IF (
        MAX ( 'Table (2)'[Index] ) = 1,
        BLANK (),
        MAX ( 'Table (2)'[value] ) - last_value
    )

Sample .pbix

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

[amitchandak]

@Anonymous , I think You need Day vs day diff with Show on row in Matrix visual . Use a date table

This Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])))
Last Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])-1))
Last Day = CALCULATE(sum('order'[Qty]), previousday('Date'[Date]))

variance = [This Day] -[Last Day]

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi



Appreciate your Kudos.