Difference of value, in a same column against the month and ID

[Anonymous]

i need to calculate the difference between different months value budgets, and the result should be the difference of two month 
against the same ID. like, e.g., ID PE-262. Now i need to find the difference between March 23 - April 23=  Variance result should be agaisnt Latest Month e.g April.23.
Apr 23 - May 23=  Variance result should be agaisnt Latest Month e.g May.23
May 23 - june 23=  Variance result should be agaisnt Latest Month e.g June.23
and so on 
or 
cur Month (May.24)- Previous (Apr.24) = Variance in cur Month May.24 then
Apr.24 - Mar.24 = Variance in Apr.24  and so on  like difference of two months and result shoul be in that latest month

Description	ID	Version	Budget	Variance
Reinforcement of Thuwal Town  KAUST 2	PE-262	01-Mar-23	30,018,685.35	0
SITE MANAGEMENT\OVERHEAD COST	PE-262-01	01-Mar-23	5,339,111.00	0
SM1|SITE MANAGEMENT EXP - ADMINISTRATI	PE-262-01-001	01-Mar-23	3,441,750.00	0
Salarie.Project Office Team	PE-262-01-001-01	01-Mar-23	909,050.00	0
Salaries Site Office Team	PE-262-01-001-02	01-Mar-23	791,350.00	0
Reinforcement of Thuwal Town  KAUST 2	PE-262	01-Apr-23	30,018,685.35	Mar.23-Apr.23
SITE MANAGEMENT\OVERHEAD COST	PE-262-01	01-Apr-23	5,339,111.00
SM1|SITE MANAGEMENT EXP - ADMINISTRATI	PE-262-01-001	01-Apr-23	3,441,750.00
Salarie.Project Office Team	PE-262-01-001-01	01-Apr-23	909,050.00
Salaries Site Office Team	PE-262-01-001-02	01-Apr-23	791,350.00
Reinforcement of Thuwal Town  KAUST 2	PE-262	01-May-23	30,018,685.35	apr.23-may.23
SITE MANAGEMENT\OVERHEAD COST	PE-262-01	01-May-23	5,339,111.00
SM1|SITE MANAGEMENT EXP - ADMINISTRATI	PE-262-01-001	01-May-23	3,441,750.00
Salarie.Project Office Team	PE-262-01-001-01	01-May-23	909,050.00
Salaries Site Office Team	PE-262-01-001-02	01-May-23	791,350.00
Reinforcement of Thuwal Town  KAUST 2	PE-262	01-Jun-23	30,018,685.35
SITE MANAGEMENT\OVERHEAD COST	PE-262-01	01-Jun-23	5,339,111.00
SM1|SITE MANAGEMENT EXP - ADMINISTRATI	PE-262-01-001	01-Jun-23	3,441,750.00
Salarie.Project Office Team	PE-262-01-001-01	01-Jun-23	909,050.00
Salaries Site Office Team	PE-262-01-001-02	01-Jun-23	791,350.00
Reinforcement of Thuwal Town  KAUST 2	PE-262	01-Jul-23	30,018,685.35
SITE MANAGEMENT\OVERHEAD COST	PE-262-01	01-Jul-23	5,339,111.00
SM1|SITE MANAGEMENT EXP - ADMINISTRATI	PE-262-01-001	01-Jul-23	3,441,750.00
Salarie.Project Office Team	PE-262-01-001-01	01-Jul-23	909,050.00
Salaries Site Office Team	PE-262-01-001-02	01-Jul-23	791,350.00
PE-262	PE-262	01-Aug-23	30,018,685.35
SITE MANAGEMENT\OVERHEAD COST	PE-262-01	01-Aug-23	5,339,111.00
SM1|SITE MANAGEMENT EXP - ADMINISTRATI	PE-262-01-001	01-Aug-23	3,441,750.00
Salarie.Project Office Team	PE-262-01-001-01	01-Aug-23	909,050.00
Salaries Site Office Team	PE-262-01-001-02	01-Aug-23	791,350.00
PE-262	PE-262	01-Sep-23	30,018,685.35
SITE MANAGEMENT\OVERHEAD COST	PE-262-01	01-Sep-23	5,339,111.00
SM1|SITE MANAGEMENT EXP - ADMINISTRATI	PE-262-01-001	01-Sep-23	3,441,750.00
Salarie.Project Office Team	PE-262-01-001-01	01-Sep-23	909,050.00
Salaries Site Office Team	PE-262-01-001-02	01-Sep-23	791,350.00
Reinforcement of Thuwal Town  KAUST 2	PE-262	01-Oct-23	29,016,727.35
SITE MANAGEMENT\OVERHEAD COST	PE-262-01	01-Oct-23	5,339,111.00
SM1|SITE MANAGEMENT EXP - ADMINISTRATI	PE-262-01-001	01-Oct-23	3,441,750.00
Salarie.Project Office Team	PE-262-01-001-01	01-Oct-23	909,050.00
Salaries Site Office Team	PE-262-01-001-02	01-Oct-23	791,350.00
Reinforcement of Thuwal Town  KAUST 2	PE-262	01-Nov-23	29,017,027.35
SITE MANAGEMENT\OVERHEAD COST	PE-262-01	01-Nov-23	5,339,111.00
SM1|SITE MANAGEMENT EXP - ADMINISTRATI	PE-262-01-001	01-Nov-23	3,441,750.00
Salarie.Project Office Team	PE-262-01-001-01	01-Nov-23	909,050.00
Salaries Site Office Team	PE-262-01-001-02	01-Nov-23	791,350.00
Reinforcement of Thuwal Town  KAUST 2	PE-262	01-Dec-23	29,018,487.35
SITE MANAGEMENT\OVERHEAD COST	PE-262-01	01-Dec-23	5,339,111.00
SM1|SITE MANAGEMENT EXP - ADMINISTRATI	PE-262-01-001	01-Dec-23	3,441,750.00
Salarie.Project Office Team	PE-262-01-001-01	01-Dec-23	909,050.00
Salaries Site Office Team	PE-262-01-001-02	01-Dec-23	791,350.00
Reinforcement of Thuwal Town  KAUST 2	PE-262	01-Jan-24	29,018,487.35
SITE MANAGEMENT\OVERHEAD COST	PE-262-01	01-Jan-24	5,339,111.00
SM1|SITE MANAGEMENT EXP - ADMINISTRATI	PE-262-01-001	01-Jan-24	3,441,750.00
Salarie.Project Office Team	PE-262-01-001-01	01-Jan-24	909,050.00
Salaries Site Office Team	PE-262-01-001-02	01-Jan-24	791,350.00
Reinforcement of Thuwal Town  KAUST 2	PE-262	01-Feb-24	29,018,487.35
SITE MANAGEMENT\OVERHEAD COST	PE-262-01	01-Feb-24	5,339,111.00
SM1|SITE MANAGEMENT EXP - ADMINISTRATI	PE-262-01-001	01-Feb-24	3,441,750.00
Salarie.Project Office Team	PE-262-01-001-01	01-Feb-24	909,050.00
Salaries Site Office Team	PE-262-01-001-02	01-Feb-24	791,350.00
Salarie.Site Support Team	PE-262-01-001-03	01-Feb-24	1,054,500.00
Reinforcement of Thuwal Town  KAUST 2	PE-262	01-Mar-24	29,073,487.35
SITE MANAGEMENT\OVERHEAD COST	PE-262-01	01-Mar-24	5,339,111.00
SM1|SITE MANAGEMENT EXP - ADMINISTRATI	PE-262-01-001	01-Mar-24	3,441,750.00
Salarie.Project Office Team	PE-262-01-001-01	01-Mar-24	909,050.00
Salaries Site Office Team	PE-262-01-001-02	01-Mar-24	791,350.00
Reinforcement of Thuwal Town  KAUST 2	PE-262	01-Apr-24	29,073,487.35
SITE MANAGEMENT\OVERHEAD COST	PE-262-01	01-Apr-24	5,339,111.00
SM1|SITE MANAGEMENT EXP - ADMINISTRATI	PE-262-01-001	01-Apr-24	3,441,750.00
Salarie.Project Office Team	PE-262-01-001-01	01-Apr-24	909,050.00
Salaries Site Office Team	PE-262-01-001-02	01-Apr-24	791,350.00	apr.24-mar.24

[Anonymous]

Also for 2nd code it's showing circular dependency ?

[Anonymous]

Dear @VahidDM 
Thank you for your response,
as i have tried your first code, its not getting variance , as from data in Sep.23- Oct-23= Varaicne like 1 M agaist PE-262 ID , but its showing zero???

[VahidDM]

Hi @Anonymous 

Try this:
Create a calculated column for previous month's budget:

PreviousMonthBudget = 
VAR CurrentDate = 'Table'[Version]
VAR PreviousMonthDate = EDATE(CurrentDate, -1)
RETURN
    CALCULATE(
        MAX('Table'[Budget]),
        'Table'[ID] = EARLIER('Table'[ID]),
        'Table'[Version] = PreviousMonthDate
    )

Create a calculated column for variance

Variance = 
IF(
    ISBLANK('Table'[PreviousMonthBudget]),
    BLANK(),
    'Table'[Budget] - 'Table'[PreviousMonthBudget]
)

Create a measure to display the variance for the latest month

LatestMonthVariance = 
VAR LatestMonth = CALCULATE(
    MAX('Table'[Version]),
    ALL('Table')
)
RETURN
IF(
    MAX('Table'[Version]) = LatestMonth,
    CALCULATE(
        SUM('Table'[Variance]),
        FILTER(
            'Table',
            'Table'[Version] = LatestMonth
        )
    ),
    BLANK()
)

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

Appreciate your Kudos!! 

LinkedIn|Twitter|Blog |YouTube