Need Help with Dax

[shahrukhgaffar0]

Hi Power BI Experts,

I Need Help with Dax Calculation

I Have Table in Power Bi Sales_Data
This Table has
ColumnsCon
ProjectName,| Contract Value | AwardDate | CRV_Q1 |CRV_Q1_Date | CRV_Q2 |  CRV_Q2_Date | CRV_Q3 |CRV_Q3_Date | CRV_Q4  |  CRV_Q4_Date
ProjectA | 50,00,000 | 15th Aug 2025 | 5800000 | 15th Feb 2026 | 57500000 | 16th June 2026 | 6500000 | 30th Sept 2025 | 5500000 | 30th Dec 2025

this is My Project and CVR means (Contract Revised Value) 
Now i want To Present This commulative in Bar Chart


I have Dim Table as Dim_Calender which has 1 to Many Relationship with AwardDate
I Want to Populate my Values Like this
Since Our Q1 and Q2 is for 2026 and Q3 & Q4 for 2025 
I have slicer in Report for Year if i click on Slicer
For 2025 It Shows Like This

for 2026

I am Not able to get a Proper Way to Populate My contract Value based on this

[Praful_Potphode · Accepted solution]

Hi @shahrukhgaffar0 ,

try sample pbix.

Please give kudos or mark it as solution once confirmed.

Thanks and Regards,

Praful

[Kedar_Pande]

@shahrukhgaffar0 

Cumulative Value =
VAR CurrentDate = MAX('Dim_Calendar'[Date])
RETURN
CALCULATE(
SUMX(
Sales_Data,
COALESCE(
SWITCH(
TRUE(),
Sales_Data[CRV_Q4_Date] <= CurrentDate, Sales_Data[CRV_Q4],
Sales_Data[CRV_Q3_Date] <= CurrentDate, Sales_Data[CRV_Q3],
Sales_Data[CRV_Q2_Date] <= CurrentDate, Sales_Data[CRV_Q2],
Sales_Data[CRV_Q1_Date] <= CurrentDate, Sales_Data[CRV_Q1],
Sales_Data[AwardDate] <= CurrentDate, Sales_Data[Contract Value],
0
),
0
)
),
Sales_Data[AwardDate] <= CurrentDate
)

Relate Dim_Calendar[Date] → Sales_Data[AwardDate]

Bar chart: X-axis = Dim_Calendar[Month-Year], Y-axis = [Cumulative Value]

Year slicer on Dim_Calendar[Year]

If this answer helped, please click Kudos or Accept as Solution.
-Kedar
LinkedIn: https://www.linkedin.com/in/kedar-pande

[amitchandak]

@shahrukhgaffar0 , if possible, we can transform the data in power query- Double Unpivot and Reshape Data for Analysis in Power BI - https://www.youtube.com/watch?v=Diazpx1Nfec

[shahrukhgaffar0]

Yes, We Can do this using Unpivoting the Data But there are Many things which I think I lost that One since this is Only i showed My Require columns but we are Consider Upto 15 Columns to be in Model and Then Also We have Many Project names

Is this Possible Becuase I created a Calcualted Table but I failed to Populate this Data 

CVR_Events =

 VAR TABLE_ = UNION(

    SELECTCOLUMNS(

        'Sales Data',

        "ProjectID", 'Sales Data'[Opportunity ID],

        "EventDate", 'Sales Data'[Project Award Date],

        "EventValue", 'Sales Data'[Contract Value],

        "EventType", "Contract"

    ),

    SELECTCOLUMNS(

        'Sales Data',

        "ProjectID", 'Sales Data'[Opportunity ID],

        "EventDate", 'Sales Data'[Q1_CVR_Date__c],

        "EventValue", 'Sales Data'[Q1_CVR_Value__c],

        "EventType", "Q1"

    ),

    SELECTCOLUMNS(

        'Sales Data',

        "ProjectID", 'Sales Data'[Opportunity ID],

        "EventDate", 'Sales Data'[Q2_CVR_Date__c],

        "EventValue", 'Sales Data'[Q2_CVR_Value__c],

        "EventType", "Q2"

    ),

    SELECTCOLUMNS(

        'Sales Data',

        "ProjectID", 'Sales Data'[Opportunity ID],

        "EventDate", 'Sales Data'[Q3_CVR_Date__c],

        "EventValue", 'Sales Data'[Q3_CVR_Value__c],

        "EventType", "Q3"

    ),

    SELECTCOLUMNS(

        'Sales Data',

        "ProjectID", 'Sales Data'[Opportunity ID],

        "EventDate", 'Sales Data'[Q4_CVR_Date__c],

        "EventValue", 'Sales Data'[Q4_CVR_Value__c],

        "EventType", "Q4"

    )

)

RETURN
FILTER(TABLE_,NOT ISBLANK([EventDate]))

[amitchandak]

@shahrukhgaffar0 , this table should be done in power query, will be more dynamic. Check the video in last post 

Now, if you select a year and you need the year cumulative, you can use ytd 

example measures 
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

If you want cumulative across year use this 

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all('Date'),'Date'[date] <=max('Date'[date])))
or

Cumm Based on Date = CALCULATE([Net], Window(1,ABS,0,REL, ALL('date'[date]),ORDERBY('Date'[date],ASC)))