Forum Discussion

Helper III

1 year ago

Solved

Difference with previous Date's Value

This is My Data:

I export this in Power BI.

Then I create below matrix:

Here ,

DD_MM = FORMAT(Sheet1[Date], "DD-MMM")

Avg_Value = AVERAGE(Sheet1[Value])

I want below output:

I explain term one by one:

1) Previous_Value = This value is previous date's value in same category.

2) Diff = This is the difference between Previous_Value and Avg_Value.

By this I can see what is the difference between day to day values.

So, How I can get this result? Please Help me.

Try

Previous Value =
VAR BaseTable =
    CALCULATETABLE (
        SUMMARIZE ( Sheet1, Sheet1[Date], Sheet1[Category] ),
        ALLSELECTED ( Sheet1 )
    )
VAR PrevRow =
    OFFSET (
        -1,
        BaseTable,
        ORDERBY ( Sheet1[Date], ASC ),
        PARTITIONBY ( Sheet1[Category] )
    )
VAR Result =
    CALCULATE ( [Avg_Value], PrevRow )
RETURN
    Result

Firstly i created a measure for the average value

AverageValue = 
AVERAGE(TestTable[Value])

Then a new measure for the previous row value based on max date in each category

PrevAverageValue = 
VAR CurrentCategory = MAX('TestTable'[Category])  -- Get the current row's category
VAR CurrentDate = MAX('TestTable'[Date])  -- Get the current row's date
VAR PrevDate = 
    CALCULATE(
        MAX('TestTable'[Date]),
        FILTER(
            ALL('TestTable'),  -- Ignore any filters (e.g., from the matrix row context)
            'TestTable'[Category] = CurrentCategory && 
            'TestTable'[Date] < CurrentDate  -- Get the previous date for the same category
        )
    )  -- Find the previous date for the same category
RETURN
    CALCULATE(
        [AverageValue],  -- Get the value for the previous date
        'TestTable'[Date] = PrevDate && 'TestTable'[Category] = CurrentCategory
    )

Now you can create a new measure to display your variance.

Variance = 
VAR _Numerator = [AverageValue]
VAR _Denominator = [PrevAverageValue]

RETURN 
_Numerator - _Denominator // Abs value
// DIVIDE((_Numerator -_Denominator), _Denominator, 0) // Variance %

6 Replies

Solution Supplier

Firstly i created a measure for the average value

AverageValue = 
AVERAGE(TestTable[Value])

Then a new measure for the previous row value based on max date in each category

PrevAverageValue = 
VAR CurrentCategory = MAX('TestTable'[Category])  -- Get the current row's category
VAR CurrentDate = MAX('TestTable'[Date])  -- Get the current row's date
VAR PrevDate = 
    CALCULATE(
        MAX('TestTable'[Date]),
        FILTER(
            ALL('TestTable'),  -- Ignore any filters (e.g., from the matrix row context)
            'TestTable'[Category] = CurrentCategory && 
            'TestTable'[Date] < CurrentDate  -- Get the previous date for the same category
        )
    )  -- Find the previous date for the same category
RETURN
    CALCULATE(
        [AverageValue],  -- Get the value for the previous date
        'TestTable'[Date] = PrevDate && 'TestTable'[Category] = CurrentCategory
    )

Now you can create a new measure to display your variance.

Variance = 
VAR _Numerator = [AverageValue]
VAR _Denominator = [PrevAverageValue]

RETURN 
_Numerator - _Denominator // Abs value
// DIVIDE((_Numerator -_Denominator), _Denominator, 0) // Variance %

MHTANK
Helper III
1 year ago
Yes, This is also work.
Thank you so much to you too.👍

Super User

You could create a measure like

Previous Value =
VAR PrevRow =
    OFFSET (
        -1,
        ALLSELECTED ( Sheet1 ),
        ORDERBY ( Sheet1[Date], ASC ),
        PARTITIONBY ( Sheet1[Category] )
    )
VAR Result =
    CALCULATE ( [Avg_Value], PrevRow )
RETURN
    Result

and then the diff measure would just be one measure minus the other one.

Helper III

error comes:

OFFSET's Relation parameter may have duplicate rows. This is not allowed.

Super User

Try

Previous Value =
VAR BaseTable =
    CALCULATETABLE (
        SUMMARIZE ( Sheet1, Sheet1[Date], Sheet1[Category] ),
        ALLSELECTED ( Sheet1 )
    )
VAR PrevRow =
    OFFSET (
        -1,
        BaseTable,
        ORDERBY ( Sheet1[Date], ASC ),
        PARTITIONBY ( Sheet1[Category] )
    )
VAR Result =
    CALCULATE ( [Avg_Value], PrevRow )
RETURN
    Result