Forum Discussion

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