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
lbendlin's avatar
lbendlin
Super User
2 years ago

Not sure what you need a bridge table for?

 

 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

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.