Need Help with Dax in Report for cumulative One

[shahrukhgaffar0]

Hi Data Expert,

I need Help to resolve an Issue Link of Report is Below for sample work.

I have Table which has Below Columns 

ProjectID	ProjectName	CVR Value	CVR Type	CVR Date	Contract Value	ProjectAwardDate	Country
P12	ProjectA	55,000	Q1	30-Mar-2025	50,000	02-Feb-2025	Saudi
P12	ProjectA	58,000	Q3	15-Aug-2025	50,000	02-Feb-2025	Saudi
P13	ProjectB	120,000	Q3	25-Sep-2025	100,000	27-Aug-2025	UAE
P13	ProjectB	125,000	Q4	25-Dec-2025	100,000	27-Aug-2025	UAE
P12	ProjectA	50,000	Q4	25-Dec-2025	50,000	02-Feb-2025	Saudi

I Want Below Result

Month Cumulative Value Explanation

Jan	–	No awards
Feb	50,000	Project A awarded (contract value 50,000)
Mar	55,000	Project A revised: CVR Q1 = 55,000
Apr	55,000	No changes
May	55,000	No changes
Jun	55,000	No changes
Jul	55,000	No changes
Aug	158,000	Project A (58,000) + Project B awarded (100,000)
Sep	178,000	Project B revised to 120,000 → 58,000 + 120,000
Oct	178,000	No changes
Nov	178,000	No changes
Dec	175,000	Project A revised to 50,000 + Project B revised to 125,000 = 175,000

You Can See Above as Example
I want this Result but i am not able to Achieve this 

shahrukhkhanbi/Power-BI-INC-Report

[Praful_Potphode · Accepted solution]

Hi @shahrukhgaffar0 ,

try measure  below:

Cumulative Contract Value = 
VAR CurrentVisualDate = MAX( 'CalendarTable'[Date] )

RETURN
    SUMX(
        -- 1. Get ALL projects, ignoring the Month selection on the visual
        CALCULATETABLE(
            VALUES( 'Sales Data'[Opportunity ID] ),
            REMOVEFILTERS( 'CalendarTable' )
        ),

        -- 2. Define Project Context (Must also ignore Date filters to find the original Award Date)
        VAR ProjectAwardDate = CALCULATE( MAX( 'Sales Data'[Project Award Date] ), REMOVEFILTERS( 'CalendarTable' ) )
        VAR InitialContractValue = CALCULATE( SUM( 'Sales Data'[Contract Value] ), REMOVEFILTERS( 'CalendarTable' ) )

        -- 3. Find the LATEST Revision Date ON or BEFORE the visual date
        VAR LastRevisionDate = 
            CALCULATE(
                MAX( 'CVR_Events'[EventDate] ),
                'CVR_Events'[EventDate] <= CurrentVisualDate,
                REMOVEFILTERS( 'CalendarTable' ) 
            )

        -- 4. Retrieve the Value associated with that specific Revision Date
        VAR LastRevisionValue = 
            CALCULATE(
                SUM( 'CVR_Events'[EventValue] ),
                'CVR_Events'[EventDate] = LastRevisionDate,
                REMOVEFILTERS( 'CalendarTable' )
            )

        -- 5. Calculate Value (Only if the project is actually active)
        VAR CurrentValue = 
            SWITCH( TRUE(),
                -- Case A: Project hasn't started yet relative to visual date -> Show Nothing
                ProjectAwardDate > CurrentVisualDate || ISBLANK(ProjectAwardDate), BLANK(),

                -- Case B: There is a revision, and it happened on/after the award -> Use Revised Value
                NOT ISBLANK( LastRevisionDate ) && LastRevisionDate >= ProjectAwardDate, LastRevisionValue,

                -- Case C: Project is awarded, but no revisions yet -> Use Initial Value
                InitialContractValue
            )

        RETURN
            CurrentValue
    )

Sample PBIX.

Please give kudos or mark it as solution once confirmed.

Thanks and Regards,

Praful

[amitchandak]

@shahrukhgaffar0 , I am not sure why there is no change in the August value for project A 

P12

ProjectA

58,000

Q3

15-Aug-2025

50,000

02-Feb-2025

Saudi

Also, not able to find which is the first row. This one can not as this is having value 50,000

P12

ProjectA

55,000

Q1

30-Mar-2025

50,000

02-Feb-2025

Is there any other field to know the order of these lines. Also what is project end date 

My Idea was to have column that give valid till date 

Valid Till = COALESCE(Minx(filter('Table', [ProjectID] = EARLIER('Table'[ProjectID]) && [CVR Date] >= EARLIER([CVR Date])  || [CVR Date] >= EARLIER([ProjectAwardDate])), [CVR Date]), TODAY())

This not correct, because I am not able decide the fiest record and end date 

then I would used HR approch or allocation approch 

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZU

Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM

[shahrukhgaffar0]

ProjectID	ProjectName	CVR Value	CVR Type	CVR Date	Contract Value	ProjectAwardDate	Country
P12	ProjectA	55,000	Q1	30-Mar-25	50,000	02-Feb-25	Saudi
P12	ProjectA	58,000	Q3	15-Aug-25	50,000	02-Feb-25	Saudi
P13	ProjectB	120,000	Q3	25-Sep-25	100,000	27-Aug-25	UAE
P13	ProjectB	125,000	Q4	25-Dec-25	100,000	27-Aug-25	UAE
P12	ProjectA	50,000	Q4	25-Dec-25	50,000	02-Feb-25	Saudi

What About this?

[amitchandak]

@shahrukhgaffar0 , I have created a new Sales2 table, Please check the power query steps, and then I created dax column and measure. I have not corrected GT, but every month value I got in the visual