Calculate the difference between values 6 months apart in Power BI

[CM_Learner]

Hi All,

I have been using PowerBI for a while now, but I am not really familiar with the DAX terminology and need a bit of help here. I want to create a measure that calculates the difference between two values that are six months apart. I have a table that looks something like this:

06/01/2024	0.1
05/01/2024	-0.2
04/01/2024	-0.5
03/01/2024	0
02/01/2024	1
01/01/2024	0.5
12/01/2023	0.3

Let's assume it is currently January 2024. What I want to do is calculate the difference between the value 6 months ago (0.5) and this months value (0.1). I would like to do this on a rolling basis as new monthly information is updated in the dashboard. I am not experienced or familiar enough with DAX to be able to figure this problem out. If there is someway I should be doing this that is not a measure and is easier, please point me in that direction, as well.

Thank you in advance for any assistance!

[Anonymous · Accepted solution]

Hi,@CM_Learner Hello,@bhanu_gautam ,thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.
I am glad to help you.

Have you solved your problem yet?

If the data in your datasheet is updated in real time, and the value for the current month is updated every month, and you want to implement a rolling calculation

You can refer to my test below

Here is the DAX code:

M_1 = 
VAR max_date= CALCULATE(MAX('Table'[Date]),ALL('Table'))
//max_date:Ignore filters and always get the latest time in a table that is constantly updated
VAR six_BeforeDate= EOMONTH(max_date,-7)+1

VAR values_least=CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'[Date]),'Table'[Date]=max_date))
VAR values_lastsixmonth=CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'[Date]),'Table'[Date]=six_BeforeDate))
RETURN values_least-values_lastsixmonth

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

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

[bhanu_gautam]

@CM_Learner , You can create a DAX measure using LAG function

Difference = 
VAR CurrentMonthValue = [Value]
VAR SixMonthsAgoValue = LAG([Value], 6)
RETURN CurrentMonthValue - SixMonthsAgoValue

[CM_Learner]

Hi, thank you for the feedback. Will this allow the calculation to be relative to the current date or this a static calculation? I need it to roll over into the next month and use the new 6 months values to claculate it each month.

[bhanu_gautam]

That is static if you want dynamic you can try

Difference = 
VAR CurrentMonthValue = [Value]
VAR SixMonthsAgoValue = CALCULATE(
    [Value],
    DATEADD('Table'[Date], -6, MONTH)
)
RETURN CurrentMonthValue - SixMonthsAgoValue

[CM_Learner]

What should I be using for the [Value]? I have tried the name of the column, I have tried the 'Table'[Name of column] format. None of these syntaxes are working. It is saying the column doesn't exist