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