Forum Discussion

  • shahrukhgaffar0's avatar
    shahrukhgaffar0
    Regular Visitor
    9 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
      Icon for Super User rankSuper User
      9 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)))