Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Creating Revenue Measure using 2 Fact Tables (Different Granularities)

Hi All,    Hoping to get some advise on developing the correct measure which returns the revenue for records which match a critiera.    The specifics   2 Fact tables at for different graunulari...
Need Help
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi lbendlin,

 

See attached a link to a mock dataset which contains a few examples of data. 

 

Mock Model.pbix - Google Drive

 

In essence you'll see in Opportunity 2 & 3 that the values return the incorrect number as its contains only the sum of the record which is Won, where infact it needs to include the sum of all records for that line up until the max date. 

 

Another bit of context here, We also need to see the latest status is as at the point in time we're looking at, i.e. an op line may be Won, then lost, if the op is lost we need to exclude from the output.  The work around I've done for this is below, however, its extremely slow (visual times out after a few periods are added):

 

 

Test 4 =

VAR AsatDate =  MAX('As At Date'[Date]) + MAX('Time Table'[Time])

VAR CurrentDateInContext = SELECTEDVALUE('As At Date'[Date])
VAR PreviousDateInContext =
CALCULATE(
    MAX('As At Date'[Date]),
    ALLSELECTED('As At Date'),
    KEEPFILTERS('As At Date'[Date] < CurrentDateInContext))



VAR test = CALCULATETABLE(SUMMARIZE(FACT_Op_Line,
                                      FACT_Op_Line[Product ID],
                                      FACT_Op_Line[Stage Change],
                                      FACT_Op_Line[Created Date/Time]),
                            FACT_Op_Line[Created DateTime] <= CurrentDateInContext)
                            
                                                        
var LineID =               SELECTCOLUMNS(FILTER(
                            Index(1,
                            test,
                            Orderby(FACT_Op_Line[Created Date/Time],DESC),
                            PARTITIONBY(FACT_Op_Line[Opportunity Line Item Id])),
                            FACT_Op_Line[Stage Change] in {"Closed Won","Won"}),
                            "Line ID",FACT_Op_Line[Product ID])
 

VAR OpID = CALCULATETABLE(VALUES(FACT_Op_Table[Opportunity Id]),
           'FACT_Op_Table'[Commission Date] = "",
           'FACT_Op_Table'[Stage Name] = "Closed Won",
           'FACT_Op_Table'[Validation Stage] = "Completed",
           'FACT_Op_Table'[Created DateTime] >= PreviousDateInContext,
           'FACT_Op_Table'[Created DateTime] < CurrentDateInContext)
               

VAR TCV = CALCULATE([Total Value (Date Reporting)],
                LineID,
                OpID
           )

           Return TCV
 
 
Thanks for your help. 
 
lbendlin's avatar
lbendlin
Super User
2 years ago

You seem to have bent the opportunity field history object into a shape that it is not designed for. Use a proper data model, with Opportunity Object, Opportunity Product Object, and Calendar dimensions. Then decide if you need daily or lowr granularity for your field changes. 

 

I would challenge the Stage Change field on the Opportunity product - that's not how SFDC works.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    We've gone down this approach prodominatly because there needs to be an "As At" view for the business on both granularities. i.e. what was the record as at 1st of december 2023 vs current view. 

     

    how come on the challenge? 

     

     

    • lbendlin's avatar
      lbendlin
      Super User
      2 years ago

      You can't have partially won opportunities.

       

      The as of reports are done differently, via the event list. Possible in Power BI but better done upstream.

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        No you can't but you can have instances where we've won some parts of an opportunity but not others, which is where the product stage comes into play. 

         

        Based on what you've seen of the mock model do you think its not possible to achieve the orignal requirements?