Creating Revenue Measure using 2 Fact Tables (Different Granularities)

[Anonymous]

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 graunularities which are both audit tables. 

1. The Sale to happen/or not happen (and how this has changed over time)
2. The Product & Financials Sold (and how this has changed over time) 

Independent Date & Time tables as we need to reference a specific performance at a certain time of the month ( Day X at 17:00 for example). Similarly, this is used in our matrix filters to establish the sum of revenue for each month over a time period. Would concider using the date table in this instance, but because of the time element I havn't done this. 

The measure requirements:

1st measure: The measure is required to calculate the sum of all revenue on an product ID before a certian date. The data format is in ledger so its a requirement we sum from the first instance. - This measure we've been sucessful with, see below:  

The second measure is where elements start to have issues (least I believe this is the issue, though happy to be challenged on this). 

The second measure aims to collate together a list of sales (and their product Values) which match a criteria in a singular row. i.e. row must contain a record of 

Sales Table 

1) Validated

2) Completed

3) The row was Created before the As At date

4) Closed Won

Product Table 

1) Won

See current Formula: 

The current problem with this critiera is that it doesn't seem to be doing the accumulative Revenue at the Fact Product Table, only the accumulative value of the records which contain "Won". 

Any help on this would be appriciated and more info can be provided, if required. 

I'd also like to understand why it isn't working, so any context there would be amazing. 

Thanks, 

[lbendlin]

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

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/1447...

[Anonymous]

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]

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]

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]

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]

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?