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

Anonymous — Thu, 20 Jun 2024 07:57:37 GMT

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

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

VahidDM — Thu, 20 Jun 2024 08:10:01 GMT

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

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

Anonymous — Thu, 20 Jun 2024 08:44:48 GMT

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???

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

Anonymous — Thu, 20 Jun 2024 08:47:58 GMT

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

topic Re: Difference of value, in a same column against the month and ID in DAX Commands and Tips

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

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

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

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