calculate the variance between rows based on date and variable

[Til22]

Hi,

I am hoping some one can help me.  I have a list of variables, dates and values and I want to calculate the variance in value between dates when the variables are the same.  For example, the variance between Pineapples on 19-Aug and 18-Aug is 1, and the variance between Grapes on 19-Aug and 18-Aug is 4.

Any advise would be appreciated.

Til22

[Anonymous · Accepted solution]

Hi @Til22 

You can use EARLIER DAX to realize your needs .I create a sample ,you can refer to it .

Original Data :

Then create a calculated column to return the diff .

diff =
var previous_variance =CALCULATE(MAX('Table'[#]),FILTER('Table','Table'[Covid]=EARLIER('Table'[Covid]) && 'Table'[Date]<EARLIER('Table'[Date])))
return 'Table'[#]-previous_variance

The final result is as shown :

I have attached my pbix file ,you can refer to it .

Best Regards

Community Support Team _ Ailsa Tao

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

[VahidDM]

 Hi @Til22 

Try this measure:

Variance = 
VAR _MaxDate =
    MAX ( 'Table'[Date Reported] )
RETURN
    VAR _lastdate =
        CALCULATE (
            MAX ( 'Table'[Date Reported] ),
            FILTER ( ALL ( 'Table' ), 'Table'[Date Reported] < _MaxDate )
        )
    RETURN
        VAR _DateValue =
            MAX ( 'Table'[#] )
        RETURN
            VAR _LastDateValue =
                CALCULATE (
                    MAX ( 'Table'[#] ),
                    FILTER (
                        ALLEXCEPT ( 'Table', 'Table'[Covid Results] ),
                        'Table'[Date Reported] = _lastdate
                    )
                )
            RETURN
                _DateValue - _LastDateValue

Output will be as below:

Did I answer your question? Mark my post as a solution!

Appreciate your Kudos  !!