Forum Discussion

shahrukhgaffar0's avatar
shahrukhgaffar0
Regular Visitor
8 months ago
Solved

Need Help with Dax

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


 

" Time Intelligence"
"DAX " "DAX help" 'Need help'

5 Replies

    • shahrukhgaffar0's avatar
      shahrukhgaffar0
      Regular Visitor
      8 months ago

      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's avatar
        amitchandak
        Super User
        8 months ago

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

  • Kedar_Pande's avatar
    Kedar_Pande
    Super User
    8 months ago

    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